Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Display time differences as Weeks, Days

Solved
Jump to Solution
121 5
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi,

I'm trying to work out how to display the difference between two dates as weeks, then days.

As an example, one of our crew will be away for 9 weeks and 3 days. It is difficult for them to work out how long that is if it's only displayed in days (66) and in weeks it's not accurate enough.

If I use

DATETIME_DIFF({End Date},{Start Date},'w')

Then anything less than one week displays as 0.0, and the extra days are not accounted for.

Ideally I'd like to be able to display "9w, 3d".

Any help working this out would be greatly appreciated.

1 Solution

Accepted Solutions

Hi @mrwinter ,

This formula should do the trick

ROUNDDOWN((DATETIME_DIFF({End Date},{Start Date},'days')/7),0)&"w"&IF(

(DATETIME_DIFF({End Date},{Start Date},'days')-(ROUNDDOWN(DATETIME_DIFF({End Date},{Start Date},'days')/7,0)*7)) > 0, ", "&(DATETIME_DIFF({End Date},{Start Date},'days')-(ROUNDDOWN(DATETIME_DIFF({End Date},{Start Date},'days')/7,0)*7))&"d")

See Solution in Thread

5 Replies 5

Try using TONOW() or FROMNOW().

They are documented in the Formula Field Reference

Hi Kuovonne, thanks for your reply.

I've just taken a look at the documentation for those formula fields, but the dates I'm using are all in the future. The formula is for a worktime on and off schedule for many weeks until the end of next year. I'm not seeing how TONOW of FROMNOW are going to help me there.

I don’t know if TONOW() or FROMNOW() give a specific number of weeks, but I do recall that it gave a very human readable duration including varying units. I am away from a computer so I cannot test it.

it is possible to craft a formula that says the number of weeks and days, but it would not be a simple one function formula like TONOW() or FROMNOW(). If you want to experiment with crafting a formula with the exact output you want, look into combining both the quotient and the remainder when you divide the number of days by seven. 

Hi @mrwinter ,

This formula should do the trick

ROUNDDOWN((DATETIME_DIFF({End Date},{Start Date},'days')/7),0)&"w"&IF(

(DATETIME_DIFF({End Date},{Start Date},'days')-(ROUNDDOWN(DATETIME_DIFF({End Date},{Start Date},'days')/7,0)*7)) > 0, ", "&(DATETIME_DIFF({End Date},{Start Date},'days')-(ROUNDDOWN(DATETIME_DIFF({End Date},{Start Date},'days')/7,0)*7))&"d")

@Mohamed_Swella1This is perfect! Thanks so much for your help.