software

i have two columns of data both time formats when i subtract one from the
other it displays ###### if it is a minus value is there away to get round
this i would like to view the diffrence even if it is a minus amount.

Thanks

Simon

You can do this by setting the date system to the 1904 reference.
Go to the Tools menu, choose Options, then the Calculation tab.
Check quot;1904 date systemquot;. Note, though, that this will cause all
existing dates to be 4 years off.--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.comquot;Jelinekquot; gt; wrote in message
...
gt;i have two columns of data both time formats when i subtract one
gt;from the
gt; other it displays ###### if it is a minus value is there away
gt; to get round
gt; this i would like to view the diffrence even if it is a minus
gt; amount.
gt;
gt; Thanks
gt;
gt; Simon
Or:

=IF(A1-B1lt;0,quot;-quot;,quot;quot;)amp;TEXT(ABS(A1-B1),quot;hh:mmquot;)

If you want to calculate with it, use just =A1-B1. It will not show the way you want, but further calculations are correct.

--
Kind regards,

Niek Otten

quot;Chip Pearsonquot; gt; wrote in message ...
gt; You can do this by setting the date system to the 1904 reference. Go to the Tools menu, choose Options, then the Calculation
gt; tab. Check quot;1904 date systemquot;. Note, though, that this will cause all existing dates to be 4 years off.
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt; quot;Jelinekquot; gt; wrote in message ...
gt;gt;i have two columns of data both time formats when i subtract one from the
gt;gt; other it displays ###### if it is a minus value is there away to get round
gt;gt; this i would like to view the diffrence even if it is a minus amount.
gt;gt;
gt;gt; Thanks
gt;gt;
gt;gt; Simon
gt;
gt;
For negative dates/times pull-down:

Tools gt; Options gt; Calculation and check the 1904 date system
--
Gary's Studentquot;Jelinekquot; wrote:

gt; i have two columns of data both time formats when i subtract one from the
gt; other it displays ###### if it is a minus value is there away to get round
gt; this i would like to view the diffrence even if it is a minus amount.
gt;
gt; Thanks
gt;
gt; Simon

Try 1904 type date format:

Tools/Options/Calculation tab/(bottom left corner)

Note that changing to the 1904 date system will cause all your
existing dates to be off by 4 years and a day

OR

F1: lt;end timegt;
F2: lt;start timegt;As Excel shows negative times as ##### take largest from smallest and prefix
by negative sign.

=IF(F1lt;F2,TEXT(F2-F1,quot;-quot; amp; quot;hh:mmquot;),F1-F2)
quot;Jelinekquot; wrote:

gt; i have two columns of data both time formats when i subtract one from the
gt; other it displays ###### if it is a minus value is there away to get round
gt; this i would like to view the diffrence even if it is a minus amount.
gt;
gt; Thanks
gt;
gt; Simon

You could go into tools=gt;options, calculation tab and change to the 1904 date
system (the default for the mac). This supports negative dates, but your
dates would be off by about 4 years.

or you could use a formula like

=if(A1gt;b1,quot;-quot;amp;Text(a1-b1,quot;hh:mmquot;),Text(b1-a1,quot;hh:mmquot;))

--
Regards,
Tom Ogilvyquot;Jelinekquot; wrote:

gt; i have two columns of data both time formats when i subtract one from the
gt; other it displays ###### if it is a minus value is there away to get round
gt; this i would like to view the diffrence even if it is a minus amount.
gt;
gt; Thanks
gt;
gt; Simon