I have a SharePoint list that has the following columns:
Total Hours (Decimal)
The "Start Time" and "End Time" are DATE format, "Employee Count" is a NUMBER and the remaining two columns are Calculated (NUMBER).
"Total Hours" column works out the time difference between Start and End Time then multiplies the return value by Employee Count using the following formula:
=CONCATENATE(INT([Time Diff]*[Employee Count]*24),":",TEXT(MOD([Time Diff]*[Employee Count]*24*60,60),"00"))
I then have to convert the "Total Hours" into decimal, this is done in the "Total Hours (Decimal)" column using the following forumla:
This works great so long as the "Total Hours" column is less than 24, if it goes over 24 then I get a ?NAME error.
To get around this I've tried to put an IF statement into the "Total Hours (Decimal)" column to change the calculation depending if the value in "Total Hours" is less than 24.
=IF([Total Hours]<24,[Total Hours]*24,0)
The 0 is just a place holder as I've not worked out the right calculation yet. However this IF statement doesn't seem to work as the result is always 0 no matter if the "Total Hours" is less or greater than 24. Is there some catch with referencing a calculated column?
Any help gratefully received.
I made a mistake in saying the "Start Time" and "End Time" were DATE format, they are in fact TEXT, this is because they are being populated from a Microsoft Form, which currently doesn't have any time controls so users are inputting the time as text. Hopefully my formulas will make a bit more sense now.
The list looks like this:
The configuration for "Total Hours" is as follows:
And "Total Hours (Decimal)":