Forum Discussion
Calculated Column with hours greater than 24 in SharePoint list
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?
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)))
4 Replies
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
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.
