Time as a negative value.

[David]

Excel 2002.

I have an Excel spreadsheet which calculates the hours
worked by employees. They enter the hours they start and
finish, and the Excel formula calculates what their
flexible hours should total at the end of the week. If
they are in credit for hours worked, the worksheet is
fine and displays that their flexi is 0:30 in credit for
example. However, we want this also to display as red
when they are in debit. If they worked less than their
required hours though, the total time shows as ##########
as normal. Is there any way to show the negative time
value as -0:30 and in red?

Thanks

 

[Ron Rosenfeld]

Excel 2002.

I have an Excel spreadsheet which calculates the hours
worked by employees. They enter the hours they start and
finish, and the Excel formula calculates what their
flexible hours should total at the end of the week. If
they are in credit for hours worked, the worksheet is
fine and displays that their flexi is 0:30 in credit for
example. However, we want this also to display as red
when they are in debit. If they worked less than their
required hours though, the total time shows as ##########
as normal. Is there any way to show the negative time
value as -0:30 and in red?

Thanks

In order to display negative times (as times), you have to use the 1904 date
system (Tools/Options/...)

If you make that change, that will also change all of your dates by four years.

An alternative would be to display the result as General, and multiply by 24 to
obtain decimal time.

Finally, even though the negative time is displayed as #######, it is still
stored properly, so could be used in subsequent calculations.


--ron