subtracting date and time

[happyPotter]

Hello

I'm trying to calculate the difference between 2 dates and times

In the following I have:

A1 = 10/21/04 (which is today's current date)
A2 = 10/19/04 (date message came in)
B2 = 11:50 PM (time message came in)
C2 = 12:05 AM (time when message replied to)
D3 = ((formula that will give end result of 1 days) ---- (days it too
to reply to message from date in A1)
E3 = ((formula that will give end result of 15 minutes)----(time i
HH:MM it took to reply to message)

I have formatted the cells to be date and tim

 

[Bernie Deitrick]

Happy,

The date in A1 must be a value, and not a formula, otherwise, this will
return an incorrect value tomorrow...

In cell D3, formatted for general, this will return 1:
=INT((A1+C2)-(A2+B2))

In cell E3, formatted for hh:mm, this will return 0:15
=((A1+C2)-(A2+B2))-INT((A1+C2)-(A2+B2))

But, instead of those two, simply use:

=(A1+C2)-(A2+B2)

formatted custom [h]:mm, which will return 24:15.

HTH,
Bernie
MS Excel MVP

 

[Paul Corrado]

Happy

Assuming that the dates are integers and the times are only decimal
portions.

Then the elapsed time in days is

(A1+C2)-(A2+B2)

Format as an integer (Note, that a message received at 11:00 pm yesterday
and to which the reply was sent at 9:00 am today would be less than one day.
If you want that to be one day, then remove the references to cells C2 and
B2)

The elapsed time in Hours is the same formula formatted using the Custom
Format [h]:mm (Format/Cells & choose custom and enter that formatting code)