Re: Display time differences as Weeks, Days

Solved
1849 0
cancel
Showing results for
Did you mean:
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
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")`
5 Replies 5
18 - Pluto

Try using TONOW() or FROMNOW().

They are documented in the Formula Field Reference

5 - Automation Enthusiast

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.

18 - Pluto

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.

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

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