SharePoint calculated column, converting time into decimal then dealing with greater than 24 hours using IF

Question

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:

The configuration for "Total Hours" is as follows:

And "Total Hours (Decimal)":

Thanks

Answer 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)