Calculated Column with hours greater than 24 in SharePoint list

[Myerith Rae]

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?

[#2]

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!

[#3]

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.

[#4 · Accepted 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)))

[#5]

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