excel time calculation

[John Smith]

I have a cell (E9) that has calculated the hours worked 'D9-C9(D9>C9)'
and an adjacent cell (F9) to convert this to decimal ('E9*24'). If
there is no entry in C9 and D9, F9 reads '-24'. How do I correct this
to show 0.00?

 

[RobertVA]

I have a cell (E9) that has calculated the hours worked 'D9-C9(D9>C9)'
and an adjacent cell (F9) to convert this to decimal ('E9*24'). If
there is no entry in C9 and D9, F9 reads '-24'. How do I correct this
to show 0.00?

To do the entire job in one column:

=IF(C9<D9,(D9-C9)*24,0)

IF the value in column C is less than the value in column D the
difference is multiplied by 24. Otherwise (when the values are equal or
the start time is later than the end time) the formula returns zero.

Make sure you format the cells to display two decimal places so that the
numbers line up attractively.

Note that a more complicated formula would be required if the shift
starts one day and ends past midnight.

 

[RobertVA]

I have a cell (E9) that has calculated the hours worked 'D9-C9(D9>C9)'
and an adjacent cell (F9) to convert this to decimal ('E9*24'). If
there is no entry in C9 and D9, F9 reads '-24'. How do I correct this
to show 0.00?

Additional bonus information:

The previously submitted formula/function works FINE for shifts that
start one day and end the next! You just need to enter the full date and
time (11/9/06 2:30 AM) in the data input columns (C and D). Note that
date and time entry and display formats differ in various countries.