subtract 4000 hrs from 10000 hrs

M

MUZZA75

i have a problem at work where i have to subtract one set of hours and
minutes from another, i.e. 13234:34 - 1234:58. The problem that I have is
that excel does not recognise above 9999:99. Does anybody have an idea as to
how I can do this, be it a conversion, macro or a simple aside formula?
 
D

David McRitchie

--
Format the cell as [h]:mm
good as long as the result is zero or positive.
 
M

Myrna Larson

I think the problem may be entering the time 13234:34.

Excel 2002 treats this as text.

The limit he describes, 9999:59, is the maximum for typing in the time at the
keyboard and having it recognized as a time. This seems to be strictly a
limitation of the data-entry-parsing code. I can put =TODAY() in a cell, and
format it as [h]:mm, and I see 918216:00.

If you can live with a contrary solution, i.e. entering the time with a period
instead of a colon, and you have the Analysis Tool Pack installed,

with the numbers entered as 13234.34 in A1 and 1234.58 in A2:

=DOLLARFR(DOLLARDE(A1,60)-DOLLARDE(A2,60),60)

That gives me 11999.36 (again note there's a period rather than colon).
 
Top