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.
column 1: 16:35 column 2: 14:00
So, result should be 30:35.
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It only takes a minute to sign up.Sign up to join this community
Date calculations go from
=TEXT( Time(16,35,0)+Time(14,0,0) , "hh:mm" )
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:
So you have to:
... that is going to be long Formula..
(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
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.
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.