Calculating and formatting a duration of time as H:MM


#1

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.


#2

Thanks for an awesome Tutorial! I think it needs to be updated as it’s no longer working as a copy and paste.

You also can’t add together the outputted duration of a group of records, which is annoying.