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

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)

"PCERM" wrote in message
...
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

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" wrote:

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)

"PCERM" wrote in message
...
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

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

"PCERM" wrote in message
...
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" wrote:

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)

"PCERM" wrote in message
...
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