Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have been trying to get the difference between two dates in days hours and minutes. I can do just days or just hours and minutes. The formula I have right now is
Interval(date(Date#(ENDDATETIME,'DD/MM/YYYY hh:mm:ss'), 'DD/MM/YYYY hh:mm:ss') - date(Date#(STARTDATETIME, 'DD/MM/YYYY hh:mm:ss'), 'DD/MM/YYYY hh:mm:ss'), 'D hh:mm' )
However this doesn't format it how I want. An example of this would be
STARTDATETIME | ENDDATETIME | DURATION( CALCULATED FIELD) |
30/12/2017 10:36:00 | 31/12/2017 17:21:00 | 62 06:45 |
As you can see the calculated field using the formula is correct as it is 62 days, 6 hours and 45 minutes, but I want it formatted in a more readable way. Any help?
You could separate out the date part and time parts and concatenate strings...
Floor(Bunchastuffhere) & ' days, ' & time(Frac(Bunchastuffhere),'hh:mm')
If it's really important to you, you can floor to the nearest 1/24 to calculate the hours and then to the nearest 1/(24*60) for the minutes.
Thank you. I will try that now