please help me in something
i have two cells:
31/10/2004 18:00
01/09/2004 07:00
i want to subtract the two cells
the result i am taking is 29/02/1900 11:00
but the correct result is 29 and one month
what can i do
thanks

Hi

Try formatting the result as General. This will give you 60 days. I don't
know how the answer can be '29 and one month'!

Andy.

"kasey" wrote in message
...
please help me in something
i have two cells:
31/10/2004 18:00
01/09/2004 07:00
i want to subtract the two cells
the result i am taking is 29/02/1900 11:00
but the correct result is 29 and one month
what can i do
thanks

Thank you for your quick response. I unfortunately have tried that and it
doesn't work either. I even have some calculations that are within the same
day and the month will show up as 1, the day shows up as 0 (which is should),
and the time calculation is correct. Thank you for your help!

"Andy" wrote:

Hi

Try formatting the result as General. This will give you 60 days. I don't
know how the answer can be '29 and one month'!

Andy.

"kasey" wrote in message
...
please help me in something
i have two cells:
31/10/2004 18:00
01/09/2004 07:00
i want to subtract the two cells
the result i am taking is 29/02/1900 11:00
but the correct result is 29 and one month
what can i do
thanks

=(DAY(A1)-DAY(A2))&" days and "&(MONTH(A1)-MONTH(A2))&" months"

will get you the answer you are looking for.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"kasey" wrote in message
...
Thank you for your quick response. I unfortunately have tried that and it
doesn't work either. I even have some calculations that are within the same
day and the month will show up as 1, the day shows up as 0 (which is should),
and the time calculation is correct. Thank you for your help!

"Andy" wrote:

Hi

Try formatting the result as General. This will give you 60 days. I don't
know how the answer can be '29 and one month'!

Andy.

"kasey" wrote in message
...
please help me in something
i have two cells:
31/10/2004 18:00
01/09/2004 07:00
i want to subtract the two cells
the result i am taking is 29/02/1900 11:00
but the correct result is 29 and one month
what can i do
thanks

Hi Fred,

Just test with today (Jul 3 2005) and Dec 5 2004

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Fred Smith" wrote in message
...
=(DAY(A1)-DAY(A2))&" days and "&(MONTH(A1)-MONTH(A2))&" months"

will get you the answer you are looking for.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"kasey" wrote in message
...
Thank you for your quick response. I unfortunately have tried that and
it
doesn't work either. I even have some calculations that are within the
same
day and the month will show up as 1, the day shows up as 0 (which is
should),
and the time calculation is correct. Thank you for your help!

"Andy" wrote:

Hi

Try formatting the result as General. This will give you 60 days. I
don't
know how the answer can be '29 and one month'!

Andy.

"kasey" wrote in message
...
please help me in something
i have two cells:
31/10/2004 18:00
01/09/2004 07:00
i want to subtract the two cells
the result i am taking is 29/02/1900 11:00
but the correct result is 29 and one month
what can i do
thanks

Hi Nick,

I agree there are problems when the second day is less than the first. There are
also problems going from Feb 28th to March 31st. As others have pointed out, you
can calculate the number of days properly, but calculating months and days is
always problematic. I wanted to show these formulas in case the OP had data
(like always starting at the first of the month) which would make them useful. I
assumed he/she would post back if other scenarios needed to be considered.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"Niek Otten" wrote in message
...
Hi Fred,

Just test with today (Jul 3 2005) and Dec 5 2004

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Fred Smith" wrote in message
...
=(DAY(A1)-DAY(A2))&" days and "&(MONTH(A1)-MONTH(A2))&" months"

will get you the answer you are looking for.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"kasey" wrote in message
...
Thank you for your quick response. I unfortunately have tried that and it
doesn't work either. I even have some calculations that are within the same
day and the month will show up as 1, the day shows up as 0 (which is
should),
and the time calculation is correct. Thank you for your help!

"Andy" wrote:

Hi

Try formatting the result as General. This will give you 60 days. I don't
know how the answer can be '29 and one month'!

Andy.

"kasey" wrote in message
...
please help me in something
i have two cells:
31/10/2004 18:00
01/09/2004 07:00
i want to subtract the two cells
the result i am taking is 29/02/1900 11:00
but the correct result is 29 and one month
what can i do
thanks

http://www.cpearson.com/excel/datedif.htm

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"kasey" wrote in message
...
please help me in something
i have two cells:
31/10/2004 18:00
01/09/2004 07:00
i want to subtract the two cells
the result i am taking is 29/02/1900 11:00
but the correct result is 29 and one month
what can i do
thanks

Format your result cell as an number, rather than a date.

On Fri, 1 Jul 2005 13:36:16 -0700, "kasey"
wrote:

please help me in something
i have two cells:
31/10/2004 18:00
01/09/2004 07:00
i want to subtract the two cells
the result i am taking is 29/02/1900 11:00
but the correct result is 29 and one month
what can i do
thanks

This is a common problem given that months (and years) have a variable number
of days. So you have to decide how you want to represent certain values.

For example, given dates:

31 Jan 2005
1 Mar 2005

The Datedif function previously recommended will give an answer of
1 month -2 days

I would think 1 month 1 day makes more sense.

But, what about

28 Jan 2005
1 Mar 2005

Do you want an answer of
1 month 1 day
or
1 month 4 days

The latter would be correct if you were counting full calendar months (February
in this case) plus the days that were outside of full calendar months (28-31
Jan + 1 March). But could give answers, under certain circumstances, of more
than 31 days.


--ron

I am calculating the difference between two dates and times. For example,
6/3/2004 14:50 - 6/1/2004 12:00. The formula I am using now is
=text(a1-a2,"m, d, hh:mm"). When this formula calcuates the month
(especially in the example I just gave), the value for month is shown as one
more than what the answer should be. This is what is produces: 6/3/2004
14:50 - 6/1/2004 12:00 = 1, 2, 14:50. It should equal 0, 2, 14:50. The time
is important especially when calculation over a 24 hr period.

Any other ideas?

"Ron Rosenfeld" wrote:

On Fri, 1 Jul 2005 13:36:16 -0700, "kasey"
wrote:

please help me in something
i have two cells:
31/10/2004 18:00
01/09/2004 07:00
i want to subtract the two cells
the result i am taking is 29/02/1900 11:00
but the correct result is 29 and one month
what can i do
thanks

This is a common problem given that months (and years) have a variable number
of days. So you have to decide how you want to represent certain values.

For example, given dates:

31 Jan 2005
1 Mar 2005

The Datedif function previously recommended will give an answer of
1 month -2 days

I would think 1 month 1 day makes more sense.

But, what about

28 Jan 2005
1 Mar 2005

Do you want an answer of
1 month 1 day
or
1 month 4 days

The latter would be correct if you were counting full calendar months (February
in this case) plus the days that were outside of full calendar months (28-31
Jan + 1 March). But could give answers, under certain circumstances, of more
than 31 days.


--ron

Try this one, hope it helps.

=(MONTH(A7)-MONTH(A8))&","&(DAY(A7)-DAY(A8))&","&TEXT(A7-A8,"h:mm")


--
Nikki94
------------------------------------------------------------------------
Nikki94's Profile: http://www.excelforum.com/member.php...o&userid=24930
View this thread: http://www.excelforum.com/showthread...hreadid=384001

Here's what I got:

=DATEDIF(C35,C34,"m")&"m "&DATEDIF(C35,C34,"md")&"d"

Gives me: 1m 30d, which by my calcs, is correct.

I tried it again with 12/31/2004 - 10/16/2004 and it gave me 2m 16d.
Hope that helps.


--
centerNegative
------------------------------------------------------------------------
centerNegative's Profile: http://www.excelforum.com/member.php...o&userid=24921
View this thread: http://www.excelforum.com/showthread...hreadid=384001

On Tue, 5 Jul 2005 15:41:06 -0500, centerNegative
<centerNegative.1rpsyi_1120597519.9061@excelforu m-nospam.com wrote:


Here's what I got:

=DATEDIF(C35,C34,"m")&"m "&DATEDIF(C35,C34,"md")&"d"

Gives me: 1m 30d, which by my calcs, is correct.

I tried it again with 12/31/2004 - 10/16/2004 and it gave me 2m 16d.
Hope that helps.

Your formula does not take the times into account.

Also it (due to the way DATEDIF works) gives illogical results for certain
dates. For example:

C34: 01-Mar-2005 15:00
C35: 30-Jan-2005 07:00

I would think the "correct" answer would be:

1 month 1 day 08 h 00 min or
1 month 2 days 08 h 00 min

depending on how the OP wants to count "months".

Your formula gives the result:

1m -1d




--ron

On Tue, 5 Jul 2005 14:59:08 -0500, Nikki94
wrote:


Try this one, hope it helps.

=(MONTH(A7)-MONTH(A8))&","&(DAY(A7)-DAY(A8))&","&TEXT(A7-A8,"h:mm")

With:

A7: 01-Mar-2005 00:00
A8: 28-Jan-2005 00:00


Your formula gives:

2,-27,0:00




--ron

On Tue, 5 Jul 2005 08:35:09 -0700, "kasey"
wrote:

I am calculating the difference between two dates and times. For example,
6/3/2004 14:50 - 6/1/2004 12:00. The formula I am using now is
=text(a1-a2,"m, d, hh:mm"). When this formula calcuates the month
(especially in the example I just gave), the value for month is shown as one
more than what the answer should be. This is what is produces: 6/3/2004
14:50 - 6/1/2004 12:00 = 1, 2, 14:50. It should equal 0, 2, 14:50. The time
is important especially when calculation over a 24 hr period.

Any other ideas?

Your response does not answer the questions I posed. When you are able to do
so, I will be able to assist further.

Best,

--ron

On Tue, 5 Jul 2005 08:35:09 -0700, "kasey"
wrote:

I am calculating the difference between two dates and times. For example,
6/3/2004 14:50 - 6/1/2004 12:00. The formula I am using now is
=text(a1-a2,"m, d, hh:mm"). When this formula calcuates the month
(especially in the example I just gave), the value for month is shown as one
more than what the answer should be. This is what is produces: 6/3/2004
14:50 - 6/1/2004 12:00 = 1, 2, 14:50. It should equal 0, 2, 14:50. The time
is important especially when calculation over a 24 hr period.

Any other ideas?

In addition to not responding to my previous questions, I don't understand how
you obtain any of the results that you have posted.

In your first example, (31/10/2004 18:00) - (01/09/2004 07:00) it seems to me
the correct answer should be 1 month 30 days and not the 1 month 29 days that
you posted.

In your second example, although your dates are ambiguous I must assume that
you are using the same English format as in your initial posting
(day-month-year) so I don't understand why you expect an answer of 0, 2, 14:50

I also do not understand how you subtract 12:00 from 14:50 and get 14:50.

I would expect an answer of 2 m 0 d 02 h 50 min

If you could clear up these discrepancies, and also respond to the questions I
previously posted, I'm sure we could provide you with a solution.

I obtained the above results with the earlier date in A2, and the later date in
A1, using the formula:

=DATEDIF(INT(A2),INT(A1-(MOD(A1,1)<MOD(A2,1))),"m")&
" m " & DATEDIF(INT(A2),INT(A1-(MOD(A1,1)<MOD(A2,1))),"md")
& " d " & TEXT(MOD(A1,1)-MOD(A2,1)+(MOD(A1,1)<MOD(A2,1)),"hh \h mm \m\i\n")

BUT, there are certain dates for which this formula will misbehave and give
illogical answers. That is why we need the discrepancies cleared up and my
previous questions answered.



--ron

Ron,

Thank you for taking the time to help me out. I am trying to calculate the
difference between two dates and times so that I can put them into
classifications from hours to days. I need to use both date and time for
this because it considers the 24 hr clock that is needed for this calculation
to work.

For example:

A1: 4/18/05 15:23
A2: 4/17/05 15:05

If I am using my current formula of =text(A1-A2,"d, hh:mm"), then the result
should be 1, 00:18.

Here is where I end up having the issue:

A1: 10/1/04 10:55
A2: 7/28/04 17:45

When using the formula =text(A1-A2, "m, d, hh:mm"), the result is 3, 4,
17:10. It should be 2, 4, 17:10. The same problems happens if/when I was to
use this same formula for the first example. Obivously, the first example
formula doesn't give me all the information I need.

Does this clear up some of your questions? Or did I just re-iterate what I
had said before?

Thanks again for your time!

"Ron Rosenfeld" wrote:

On Tue, 5 Jul 2005 08:35:09 -0700, "kasey"
wrote:

I am calculating the difference between two dates and times. For example,
6/3/2004 14:50 - 6/1/2004 12:00. The formula I am using now is
=text(a1-a2,"m, d, hh:mm"). When this formula calcuates the month
(especially in the example I just gave), the value for month is shown as one
more than what the answer should be. This is what is produces: 6/3/2004
14:50 - 6/1/2004 12:00 = 1, 2, 14:50. It should equal 0, 2, 14:50. The time
is important especially when calculation over a 24 hr period.

Any other ideas?

In addition to not responding to my previous questions, I don't understand how
you obtain any of the results that you have posted.

In your first example, (31/10/2004 18:00) - (01/09/2004 07:00) it seems to me
the correct answer should be 1 month 30 days and not the 1 month 29 days that
you posted.

In your second example, although your dates are ambiguous I must assume that
you are using the same English format as in your initial posting
(day-month-year) so I don't understand why you expect an answer of 0, 2, 14:50

I also do not understand how you subtract 12:00 from 14:50 and get 14:50.

I would expect an answer of 2 m 0 d 02 h 50 min

If you could clear up these discrepancies, and also respond to the questions I
previously posted, I'm sure we could provide you with a solution.

I obtained the above results with the earlier date in A2, and the later date in
A1, using the formula:

=DATEDIF(INT(A2),INT(A1-(MOD(A1,1)<MOD(A2,1))),"m")&
" m " & DATEDIF(INT(A2),INT(A1-(MOD(A1,1)<MOD(A2,1))),"md")
& " d " & TEXT(MOD(A1,1)-MOD(A2,1)+(MOD(A1,1)<MOD(A2,1)),"hh \h mm \m\i\n")

BUT, there are certain dates for which this formula will misbehave and give
illogical answers. That is why we need the discrepancies cleared up and my
previous questions answered.



--ron

On Wed, 6 Jul 2005 11:30:05 -0700, "kasey"
wrote:

Ron,

Thank you for taking the time to help me out. I am trying to calculate the
difference between two dates and times so that I can put them into
classifications from hours to days. I need to use both date and time for
this because it considers the 24 hr clock that is needed for this calculation
to work.

For example:

A1: 4/18/05 15:23
A2: 4/17/05 15:05

If I am using my current formula of =text(A1-A2,"d, hh:mm"), then the result
should be 1, 00:18.

Here is where I end up having the issue:

A1: 10/1/04 10:55
A2: 7/28/04 17:45

When using the formula =text(A1-A2, "m, d, hh:mm"), the result is 3, 4,
17:10. It should be 2, 4, 17:10. The same problems happens if/when I was to
use this same formula for the first example. Obivously, the first example
formula doesn't give me all the information I need.

Does this clear up some of your questions? Or did I just re-iterate what I
had said before?

Thanks again for your time!


It doesn't really clear up my questions but perhaps my discussion below will
clarify the information required.

First of all, your formula will not give the results you expect (as you have
already discovered).

It appears as if now, unlike your first example, you are now using US style
dates.

That being the case, given your expected result in your second example, it
appears as if what you want to do, with regard to counting months, is to count
CALENDAR months and then add on the extra days that fall outside of a whole
calendar month, and also take into account the time.

IS THAT THE CASE???

If it is the case, then for

A1: 25-Oct-2004 10:55
A2: 05-Jul-2004 17:45

I would expect you would want an answer of:

2 months 51 days 17 h 10 min

or, in the notation you are using above 2, 51, 17:10

(The TWO (2) months are Aug and Sep; then we have 25 days, 10 hrs, 55 minutes
in October; and 26 days 6 hrs 15 min in Jul).

If that is NOT the case, then you will have to explain IN WORDS *exactly how*
you are getting the results that you have obtained.

If that IS the case, I have a UDF I can modify slightly that will accomplish
what you want.

Also, when you post dates, if there is possible confusion between date formats,
please be clear as to which format you are using. I usually assume US (m-d-y),
but in your very first example, you were using a d-m-y format.


--ron