Sum of time values for two DateTime columns

Starting from this post let’s suppose that your two DateTime Columns are

COL_DAT1
COL_DAT2

and the request is to sum the time values. The final value  ( as a text ) will be in this format hh:mm.
For this you will need to create a calculated column and there to insert the final formula

Bellow are presented each step, one by one. Based on your regional settings, could be necessary to replace “;” with “,” in final formula ( Step 3. )

Step 1. Hours

The easiest part is to add hours, and the formula for this is
=HOUR(COL_DAT1)+HOUR(COL_DAT2)

What about minutes? The easiest  way –  add minutes from both columns
(MINUTE(COL_DAT1)+MINUTE(COL_DAT2)

But what if this value exceed 60? This mean an hour at least. Well in this case the formula:
INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60)

So, the numbers of hours will be:
=HOUR(COL_DAT1)+HOUR(COL_DAT2)+INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60)

Step 2. Minutes

Let’s calculate minutes. in both cases, if minutes exceed an hour or not:
MINUTE(COL_DAT1)+MINUTE(COL_DAT2)-60*INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60)
where first 2 elements are the total number of minutes
and the third element represent 60 minutes for each entire hour.

If you would like that minutes are represented with 2 digits even if they are less than 10 you should use an IF condition:
IF(MINUTE(COL_DAT1)+MINUTE(COL_DAT2)-60*INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60)<10;”0″&(MINUTE(COL_DAT1)+MINUTE(COL_DAT2)-60*INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60));MINUTE(COL_DAT1)+MINUTE(COL_DAT2)-60*INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60))

Step 3. Presentation

The final formula will be
=(HOUR(COL_DAT1)+HOUR(COL_DAT2)+INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60))&”:”&(IF(MINUTE(COL_DAT1)+MINUTE(COL_DAT2)-60*INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60)<10;”0″&(MINUTE(COL_DAT1)+MINUTE(COL_DAT2)-60*INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60));MINUTE(COL_DAT1)+MINUTE(COL_DAT2)-60*INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60)))

This entry was posted in CalculatedColumn, Sharepoint Online. Bookmark the permalink.