Calculating Times

  • Thread starter Thread starter Dave Palmer
  • Start date Start date
D

Dave Palmer

Does anyone know how you can display negative time?
I have a formula that calculates the difference between a
total time and 7:30 minutes. This is Ok if greater than
7:30, but doesn't display(#####) if less than 7:30.

Thanks

Dave
 
1904 Date system supports negative dates/times, but not in a very useful
way.
1/1/1904
-4
---------
-1/5/1904
not the expected 12/28/1903

With time formats that support negative numbers in the 1904 system,
AM/PM is always displayed and
6:30:00 AM
-7:30:00 AM
--------
1:00:00 AM
but the underlying value is negative, so if you add an actually 1:00 AM
to that result then you will get 12:00:00 AM, which would be extremely
surprising if you weren't aware that one of the times was negative.

John Walkenbach provides functions for dealing with dates before 1900
http://j-walk.com/ss/excel/files/xdate.htm
but I do not recall seeing anything for negative time. You could use an
IF function in the calculation, and for a negative result use
"("&TEXT(b-a,timeFormat)&")" instead of a-b. Unfortunately, that would
leave you with text instead of a number, so you would also have to
adjust any formulas that use it.

Jerry
 
Dave,

If 08:00-09:00 should be 23 hours then use

=MOD(A1-B1,1)

If it should be 1 hour, use

=ABS(A1-B1)

format as time.

If you want decimal hours, multiply by 24 and format as general.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks Jerry

The TEXT function is just what I needed, especially as we
want to display the time in parentheses. Now I just have
to see if I can total a column of mixed positives and
negative times!

Regards

Dave
 
Back
Top