Aug 15 2018 02:34 PM
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?
Aug 15 2018 10:33 PM
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!
Aug 16 2018 06:54 AM
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.
Aug 16 2018 01:35 PM
SolutionI 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)))
Dec 28 2021 03:10 PM
Aug 16 2018 01:35 PM
SolutionI 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)))