SOLVED

Calculated Column with hours greater than 24 in SharePoint list

New Contributor

I am tying to make a calculated field in a custom SharePoint list that takes the total hours an employee worked in a week and then subtract the time in break codes, but SharePoint seams to have issue with showing hours greater than 24.

 

I do this all the time in Excel when is have the format set to [h]:mm:ss and have no problems, I would like to build this data on SharePoint so that I can use it in other parts of my site.

For example 33:21:49 total hours and 6:29:56 in break Excel will return 26:51:53 no issue, SharePoint returns either ?Name or #VALUE! on every entry that is over 24 hours.

 

A few things I have tried is adding the days up of that I enter the 33:21:49 as 1:09:21:49 instead. 

For the formula I've played with variations of;

=TEXT(([Total Hours]-[Total Break]),"hh:mm:ss") and =TEXT(([Total Hours]-[Total Break]),"[h]:mm:ss")

Not sure if I'm missing something for the syntax to calculate right.

 

Any suggestions?

4 Replies

I think you have to look more into date then hours as hours are only 24.. use date diff to get to the correct result!

Not all entries will be over 24, all part time employees or anyone who doesn't work the full week doesn't have this issue.

best response confirmed by Myerith Rae (New Contributor)
Solution

I kinda go it to work, it still will not display in the [h]:mm:ss format if it is lager than a day, but at least it will be a number that could be used to figure things like employee's productivity for the week (i.e. if they worked [Column Name] number of accounts over [Calculated Column] time). There is possibly a better way to do this, but that is what I eventually worked out if anyone ever needs to do the same;

 

=(SUM((TIME((REPLACE([Column], 3, 6, "")), 0, 0)), (REPLACE([Column], 1, 2, "00")), (IF(((REPLACE([Column], 3, 6, "")) > 24), 1, 0)))

 

Hi there,
Late to this discussion, looking for a solution to a similar problem - time difference of mostly less than 24 hrs, but wanna cater for when it does exceed 1 day w/o complicate the display format. My calculated column:

=CONCATENATE(INT(([Time2]-[Time1])*24), TEXT(([Time2]-[Time1]),":mm:ss")

Which gives this:
26:11:34

HTH s1