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?

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" wrote:

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?

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

"Mike H" wrote:

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" wrote:

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?

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

"jmj713" wrote:

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

"Mike H" wrote:

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" wrote:

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?

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


On Fri, 20 Mar 2009 14:46:03 -0700, jmj713
wrote:

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

"Mike H" wrote:

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" wrote:

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?

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")



jmj713 wrote:

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?

--

Dave Peterson