software

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(A1lt;7,TEXT(TIME(7-A1,0,0),quot;hh:mm:ssquot;)amp;quot;(Db)quot;,TEXT(TIME(A1-7,0,0),quot;hh:mm:s
squot;)amp;quot;(Cr)quot;)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

quot;Bob Phillipsquot; wrote:

gt; You need to calculate it as a positive number, and test the original data to
gt; see if it is less than 7, something like
gt;
gt; =IF(A1lt;7,TEXT(TIME(7-A1,0,0),quot;hh:mm:ssquot;)amp;quot;(Db)quot;,TEXT(TIME(A1-7,0,0),quot;hh:mm:s
gt; squot;)amp;quot;(Cr)quot;)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;PCERMquot; gt; wrote in message
gt; ...
gt; gt; I am trying to set up a sheet to display flexi time with all amployees
gt; gt; working a 7 hour day. When they work more than 7 hours this is calculated
gt; in
gt; gt; numbers and converted to time using the 24 hour clock method. However
gt; when
gt; gt; someone works less than 7 hours the time converison is shown as
gt; ##########.
gt; gt; Can anyone tell me how to make this display as a negative time please.
gt; gt;
gt; gt; Thanks
gt;
gt;
gt;

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

quot;PCERMquot; gt; wrote in message
...
gt; Thanks Bob
gt;
gt; That's useful. I actually managed to get around the problem in the end by
gt; adopting the 1904 date system suggested by someone else on this forum
which
gt; is within the tools, options menu. As soon as I clicked this wo apply to
the
gt; page all negatives appeared as minus times.
gt;
gt; Thanks again
gt;
gt; Peter
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; You need to calculate it as a positive number, and test the original
data to
gt; gt; see if it is less than 7, something like
gt; gt;
gt; gt;
=IF(A1lt;7,TEXT(TIME(7-A1,0,0),quot;hh:mm:ssquot;)amp;quot;(Db)quot;,TEXT(TIME(A1-7,0,0),quot;hh:mm:s
gt; gt; squot;)amp;quot;(Cr)quot;)
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;PCERMquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I am trying to set up a sheet to display flexi time with all amployees
gt; gt; gt; working a 7 hour day. When they work more than 7 hours this is
calculated
gt; gt; in
gt; gt; gt; numbers and converted to time using the 24 hour clock method. However
gt; gt; when
gt; gt; gt; someone works less than 7 hours the time converison is shown as
gt; gt; ##########.
gt; gt; gt; Can anyone tell me how to make this display as a negative time please.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt;
gt; gt;
gt; gt;