Sum of two Time columns

Question

There are two calculated columns that display times of two date columns.
I want to sum those two "time" columns. Any suggestion is greatly appreciated.

Example:
column 1: 16:35 column 2: 14:00

So, result should be 30:35.

Answer 1

Date calculations go from 23:59 to 00:00

So

=TEXT( Time(16,35,0)+Time(14,0,0) , "hh:mm" )

will produce

06:35

Let's take different values to examine edge-cases

What should 16:35 + 14:35 do?

To get to 30:70 you have to count hours and minutes

But I can't imagine any use case for that

You probably want the Duration result: 31:10

16:35 + 14:35 = 31:10

So you have to:

• count the minutes to a HH:MM notation
• convert the MM to a Number
• convert the HH to a Number
• add the two hours count to the HH Number
• create a TEXT string making it look like a hh:mm notation

... that is going to be long Formula..

Luckily I have a (private) app to build and merge Formulas:

(SharePoint is not typed, so it doesn't really care about Text or Numbers, it will proces "1" as 1)

---

Your Formula

So with 2 basefields H1 and H2 your Formula is:

=HOUR(H1)+HOUR(H2)+TEXT(TIME(0,MINUTE(H1)+MINUTE(H2),0),"hh")
 &":"
 &MID(TEXT(TIME(0,MINUTE(H1)+MINUTE(H2),0),"hh:mm"),4,2)

The MID( hh:mm) is required because TEXT(n,"mm") converts to Months, not Minutes

The Result:

Answer 2

Beside great post by Danny, I solved problem by this:

1st, I separated hours and minutes from columns into 4 columns.
example: column 1 is 12:10 and with =Left([column 1],2) I get hours and with formula =Right(Left([column 1],5),2) I get minutes.
2nd, I summed hours and than multiplied them with 60, to get value minutes and summed this value with sum of minutes' columns.
3rd, after this I had one column that represented time in minutes. I divided it with 60 in one column, output calculated column type is number with 0 decimals, by this I get whole hours, in another calculated column I used formula =MOD([minute column],60). this was for minutes. Again number type with 0 decimals is output calculated column.
4th combined column of hours with column of minutes with Concatenate.

=CONCATENATE(TEXT(hours,"0"),":",TEXT(minutes,"0"))

I had to use TEXT because my data was in number with 0 decimals and Concatenate formula would not work if I left data like that. So I had to convert it to text.

Answer 3

Should be able to do something like this:

=TEXT(Column1+Column2,"h:mm")

This will return Sum in hours and minutes.