0

I have a SharePoint list that has the following columns:

Start Time
End Time
Employee Count
Total Hours
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:

=[Total Hours]*24

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.
Rob

UPDATE

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:

enter image description here

The configuration for "Total Hours" is as follows:

enter image description here

And "Total Hours (Decimal)":

enter image description here

Thanks

4
  • Can you show examples of values for [Total Hours] and [Total Hours (Decimal)]? [Total Hours] seems to be a text field, and not sure why it would be multiplied by 24 to calculate [Total Hours (Decimal)] – Matiur Rahman Dec 1 '20 at 17:42
  • I’m having trouble understanding the first formula. Isn’t MOD([Time Diff]*[Employee Count]*24*60,60) always be 0? And also, what does the TEXT function do in this formula? It seems that you are trying to do calculation on strings ([Total Hours]) instead of numbers. – Chelsea_MSFT Dec 2 '20 at 9:06
  • Hi Matiur, Chelsea. I've updated the post, I had a made an error is saying that Start and End time columns were DATE, when in fact they are TEXT, sorry for the confusion. – RobC_CTL Dec 2 '20 at 11:05
  • @RobC_CTL Thanks for the clarification. – Chelsea_MSFT Dec 3 '20 at 6:35
1

The formula you use =IF([Total Hours]<24,[Total Hours]*24,0) will always return false for condition [Total Hours]<24 since [Total Hours] is not a valid number to compare with 24.

You can use ISERROR function to identify if the column returns a valid output instead:

=IF(ISERROR([Total Hours]*24),0,[Total Hours]*24)

enter image description here

1
  • Thanks @Chelsea_MSFT, that makes a lot more sense. Now I just need to work out how to make a value greater than 24 into a decimal. – RobC_CTL Dec 3 '20 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.