Help

Display time differences as Weeks, Days

Solved
Jump to Solution
1720 5
cancel
Showing results for 
Search instead for 
Did you mean: 
mrwinter
5 - Automation Enthusiast
5 - Automation Enthusiast

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
Mohamed_Swella1
13 - Mars
13 - Mars

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

mrwinter
5 - Automation Enthusiast
5 - Automation Enthusiast

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. 

Mohamed_Swella1
13 - Mars
13 - Mars

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")
mrwinter
5 - Automation Enthusiast
5 - Automation Enthusiast

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