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