How can I subtract one date and time from another in Excell?

[Guest]

How can I subtract one date and time from another in Excell? e.g. 7/23/04
14:00-12/07/2004 18:28:00. I would like the answer to be in days and hours.

Many thanks in advance.

 

[Frank Kabel]

Hi
just use
=A2-A1
and format the resulting cell with the custom format
d hh:mm

 

[Bernie Deitrick]

Merlin,

With the earlier date and time in cell A1, and the later in A2, simply use
the formula

=A2-A1

and format the cell with the formula for custom, using a custom format
string of

d "Days and " h "hours" m "minutes" s "seconds"

or you could shorten that to

d "Days and " h:m:s

HTH,
Bernie
MS Excel MVP

 

[Myrna Larson]

Hi, Frank:

Very strange. That format should NOT work, because d indicates the day of the
month, and there's no day 0. That said, it DOES work! I guess the reason is
the same as the explanation for the fact that if you type 0 in a cell and
format as dd/mm/yyyy you see 00/01/1900.

I guess I should have tried it before replying to the other fellow.

 

[Bernie Deitrick]

Frank,

Until Myrna's message showed up, I had no idea that you had replied to this
message (arggh - stupid news servers...).

Anyways, please accept my apologies for jumping in.

Bernie

 

[Frank Kabel]

Hi Myrna
I just forgot to add one restriction: It does not work for date ranges

31 days (as it would count starting with 1 day again: the 1st February

1900)

 

[Frank Kabel]

Hi Bernie
no need for apologies. I sometimes have the same problem with
non-visible news. So again just two minds with the same idea ))

 

[Myrna Larson]

Yes, "d" does still mean day of month, except in the special case where you
didn't supply a date, so it comes out as 0. Helpful here, but strange,
nonetheless.

 

[Ron Rosenfeld]

Hi
just use
=A2-A1
and format the resulting cell with the custom format
d hh:mm

Just a note that that custom formatting will only work if the number of days is
less than 32.


--ron