Convert Duration Format to Decimal

I have created a CURRENT DURATION column by subtracting CURRENT START from CURRENT STOP (both are date / time columns) and - as desired - it gives me elapsed hours:minutes:seconds in duration format.

So I try to do math on that column but the my reference to HOUR(DURATION) in the CURRENT ELAPSED TIME is zero - NOT 24. Thus my math doesn’t work (I should get 24.5).

How to I get hours and minutes out of the elapsed time column in duration format to create a decimal hours elapsed (looking to get 24.5 in this example)?

Thanks

Motte

Round your duration.

Round(d,1)

Thanks Jeff - I’m not sure that’s what I’m looking for. I’m trying to find out how to reference HOURS and MINUTES in a DURATION column that is the result of a STOP date and time minus a START date and time.

HOUR(DURATION) is only 3 if 27 hours have passed. It doesn’t return the expected 27 hours.

Am I missing something in your explanation?

To simplify - if I have a value of 28:30:00 in a DURATION column that is the result of subtracting one date and time from another, how to I reference the 28 and the 30 in a math function? HOUR(DURATION) doesn’t return 28 - it returns 4.

All date math calculates as days or decimals of days. Your stop-start will result in the number of days difference, but formats that result as a duration. If you want that as hours, multiply by 24. Then round that result to break it out of the duration format. So to modify the formula I gave you…

Round(d*24,1)

Don’t try to pick out parts of the duration because it’s not actually a time.

Wow. Thank you so much. I’ve tried many different approaches. I had to use ROUND(D24,2) to get the minutes right. 45 minutes showed up as .80. With ROUND(D24,2) rather than ROUND(D*24,1) it got the minutes as .75.

Here’s the detail screenshot for the next poor sap that is spending all night trying to do this.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.