Forum Discussion
CauseAndEffect
6 years agoHelper I
Counting the time between two date
Hey people!
We want to measure time time between two dates (two separate columns with the same format)
Format1 : YYYY-MM-DD HH:MM:SS
Format2: YYYY-MM-DD HH:MM:SS
The needed output would be a column showing: HH:MM:SS with Date/time format, to be able to make calculations.
Tried the datefiff function but the return is only a singular whole number.
Time DIFF = 2019-09-21 10:16:39; 2019-09-21 10:21:22; MINUTE)
Output: 5
Wanted output: 00:04:43
I'm able to achieve this by using a function I found online:
But this returns the time as text, and therefore I cannot make any calculations.
Thankful for any tips or insights!
Hi CauseAndEffect ,
Just minus two dates. Then set the date type to be Time ( HH:mm:ss ).
Here is the result.
3 Replies
- CauseAndEffectHelper I
Relised that the topic was moved, here is the function:
Duration = // Duration formatting // * @konstatinos 1/25/2016 // * Given a number of seconds, returns a format of "hh:mm:ss" // // We start with a duration in number of seconds VAR Duration = [Duration in Seconds] // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number // These intermediate variables ensure that we have leading zero's concatenated onto single digits // Hours with leading zeros VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) ) // Minutes with leading zeros VAR M = IF ( LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ) ) // Seconds with leading zeros VAR S = IF ( LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ) ) // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" RETURN CONCATENATE ( H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) ) ) - v-eachen-msftCommunity Support
Hi CauseAndEffect ,
Just minus two dates. Then set the date type to be Time ( HH:mm:ss ).
Here is the result.- CauseAndEffectHelper I
Thanks! This worked!