How do I display time in a negative format?

[Guest]

I am trying to set up a sheet to display flexi time with all amployees
working a 7 hour day. When they work more than 7 hours this is calculated in
numbers and converted to time using the 24 hour clock method. However when
someone works less than 7 hours the time converison is shown as ##########.
Can anyone tell me how to make this display as a negative time please.

Thanks

 

[Bob Phillips]

You need to calculate it as a positive number, and test the original data to
see if it is less than 7, something like

=IF(A1<7,TEXT(TIME(7-A1,0,0),"hh:mm:ss")&"(Db)",TEXT(TIME(A1-7,0,0),"hh:mm:s
s")&"(Cr)")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thanks Bob

That's useful. I actually managed to get around the problem in the end by
adopting the 1904 date system suggested by someone else on this forum which
is within the tools, options menu. As soon as I clicked this wo apply to the
page all negatives appeared as minus times.

Thanks again

Peter

 

[Bob Phillips]

I never use the 1904 system as it differs from the 1900 system by 4 years
(no!), and this is too problematical to bother with IMO.

Glad you are sorted though.

Bob