Calculating and formatting a duration of time as H:MM

[Gary_Brocks]

This code is used in a “Duration” formula field in an Activity tracking database.
The formula calculates the hours and minutes of a duration of time (for an activity), and maintains a two digit display for minutes (H:MM) when the minutes portion of a duration is between 1 & 9 minutes. Without this code, a duration with a value for minutes between 1 & 9, for example a duration of 1 hour and 3 minutes, would display incorrectly as “1:3”, instead of correctly as “1:03”.

THE FIELDS IN THE CODE OF THE FORMULA ARE:

• Date & Time: A date field. The user enters the starting date and time of an activity.
• End Time: A date field. The user enters the ending date and time of an activity.
• Duration: The formula field. The the code of the formula will calculate the duration of the activity. The duration will be displayed in the format H:MM. If the duration is less than an hour, the H will display a zero. If the minutes are less than 10 (but greater than zero), the minutes ten’s place will display a zero. If the Date or End Time fields are blank the Duration field will be blank…

THE DURATION FORMULA FIELD CODE:

IF(OR({End Time}=BLANK(),{Date & Time}=BLANK()), “”,INT(DATETIME_DIFF({End Time},{Date & Time},‘m’)/60) & IF(MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60)<10,":0"&MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60),":"&MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60)))

CODE SECTIONS DOCUMENTATION:

IF(OR({End Time}=BLANK(),{Date & Time}=BLANK()), “”

EXPLANATION:
If the End Time field or the Date field are blank, prevent the display of the word “ERROR” that would be generated by the code that follows.

INT(DATETIME_DIFF({End Time},{Date & Time},‘m’)/60) &

EXPLANATION:
Return the hour portion of the duration calculation. This will be be concatenated with one of two versions of the minute portion of the duration in the code that follows. The minute potion will be preceded by a colon).

& IF(MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60)<10,":0"&MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60)

EXPLANATION:
If the minute portion of the duration is a value less than 10 (a single digit value), display the minute portion with a zero in tens place.

“:”&MOD(DATETIME_DIFF({End Time},{Date & Time},‘m’),60)))

EXPLANATION:

Otherwise, the minute portion of the duration will be a value of at least 10 (a two digit value). Display that value.

[Brett_Snelgrove]

Brilliant thank you Justin. Learning a new platform often takes learning these little things such as how they handle formulas and whatnot. Much appreciated.

[John_Pollard]

Gee, thanks for making this duration calc simple and easy to understand and implement. Well done.

[Sean_Lake1]

Curious on this one:
DATETIME_DIFF({End Date and TIme(Local)}, {Start date and Time(Local)}, “minutes”)
I changed the seconds to minutes but now I get:

I’d like it to be (hours) for the duration to show 2:00 based on this much shorter formula.
Also using all this, if let’s say the time is not an even, let’s say 2:00 hours, and maybe it goes from 7am-930am, would it show 2:30?

For now I could take away the formatting for now, so that it at least just shows 2.

Thanks!

[kuovonne]

@Sean_Lake1 You need to change minutes to seconds In your DATETIME_DIFF function.

[Darcie_Tanner1]

Hi all, 

Hoping someone can help. I'm trying to create a time sheet where the user inputs the hours & minutes they worked in 15min increments - so 1.25 (1 hour 15 mins), 1.5 (1hr 30 mins) etc.

How do I get this to appear as 1hr 15 mins, 1hr 30 mins in an hh:mm format without asking them to do their time sheets in seconds and also have the ability to roll up the totals?

Ideally they would not be tracking start end times using date fields. Just a box yo input a decimal. 

Example: 

This is a platform I've used for timesheets in the past, Synergist

Thank you!