# Sum of two Time columns

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.

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)

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:

• Thank You very much on your time and effort! Really appreciated. Mar 24, 2017 at 11:52

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.

• All roads lead to Rome... Note that `"x"&"y"&"z"` does the same as the concatenate function. Mar 24, 2017 at 12:34

Should be able to do something like this:

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

This will return Sum in hours and minutes.

• Thank you for your time, but this formula cannot be, not in this format, implemented to my request. Due to fact formula would calculated only until 24:00 or 00AM if result is higher, I get error response #VALUE! Mar 24, 2017 at 10:06