Skip to main content

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.

Try using TONOW() or FROMNOW().

They are documented in the Formula Field Reference. 

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 @mrwinter11 ,

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.

Terms & ConditionsAccessibility statement