Difference between dates and times

[jmj713]

I'm having real trouble coming up with a formula to get a difference between
two cells which contain full dates and times.

If I have 2/2/09 8:00 AM in A1 and 4/2/09 12:10 PM in B2, in the third cell
I entered =B1-A1 and formatted the cell as Custom and Type: d "days" hh
"hours" mm "minutes" - but I'm not getting the right number of days. The
output says: 28 days 04 hours 10 minutes, so the hours and minutes is
correct, but not the days. How can I fix this?

 

[Mike H]

Hi,

When posting date examples it's a good idea to post days greater than 12 so
we know which is the month and which are days.

I think you mean
2 February 2009 08:00

and

2 April 2009 12:10

If you do then i have do difficulty with excels answer of 28 days 4 hrs and
10 minutes, why do you think this is incorrect

Mike

 

[jmj713]

Yes, that is what I meant, but should the number of days be 59?

 

[Mike H]

hi,

Apologies, I had an elderly monent, try this

=CONCATENATE(TRUNC(TEXT(B1-A1,"[h]")/24)," days, ",HOUR(B1-A1)," hours
",MINUTE(B1-A1)," minutes")

Mike

 

[Gord Dibben]

Correct.

When you subtract first date from second date you get the number 59 if you
format the cell as General.

But......................

59 is the serial number for February 28, 1900 so with d "days" you're
getting 28 "days" because d is 28 in this case.

Now you're going to ask how to overcome this.

I'm not sure myself but someone will know and post you the correct answer.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Using this format:
d "days"...

d is limited to 0 to 31. Then it starts wrapping.

You could return a text value using a formula like:

=INT(A2-A1) & " " &TEXT(A2-A1,"hh:mm:ss")