4

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.

5

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:

  • Thank You very much on your time and effort! Really appreciated. – Danilo Mar 24 '17 at 11:52
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.

  • 1
    All roads lead to Rome... Note that "x"&"y"&"z" does the same as the concatenate function. – Danny '365CSI' Engelman Mar 24 '17 at 12:34
1

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! – Danilo Mar 24 '17 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.