excel time calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Smith - 09 Nov 2006 02:22 GMT

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?

Reply to this Message

RobertVA - 09 Nov 2006 06:48 GMT

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

Reply to this Message

RobertVA - 09 Nov 2006 07:32 GMT

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.

Reply to this Message

Rate this thread: