My discovery on adding months and days to a date!

[Epinn]

I found the following on a web site.

********************************************************************************************************

For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula

=DATE(YEAR(A1),MONTH(A1)+6, DAY(A1)+10)

gives 3/7/98.

*********************************************************************************************************

I insert the formula and 8/25/97 in a worksheet and sure enough Excel gives me 3/7/98. I understand that Excel adds the months *first* before it adds the days. Evaluate formula confirms this.

However, I get a different result when I break up the above process into two and change the sequence of addition. This is what I have done.

I enter 8/25/97 into A1. Then I enter into B1 this formula

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+10)

Please note that I only add 10 days. The result in B1 is 9/4/1997.

Then in C1, I enter

=DATE(YEAR(B1),MONTH(B1)+6,DAY(B1))

After adding six months, I get 3/4/1998 in C1.

As you will have noted, this result is different from 3/7/98 as stated above. In both Excel processes, I add six months and 10 days to 8/25/97. But the results are so different depending on the sequence of addition. (I do know that if I split the process into two and add months *first* and then days, I'll get 3/7/1998).

If I am not using Excel, I am inclined to add days before I add months as I want to go with the smallest unit first. Is it just me? What is the habit of others? In an office environment, if someone wants to add six months and 10 days, which of the above result is really expected?

Comments welcome.

Epinn

 

[Kevin James]

Hi Eppinn,

It depends on which month and therefore "What is a month"?

Sometimes its 28 days, 29 days , 30 days and others it is 31.

Depends on the particular month just what effect adding 10 days
will have on the actual day value, as you illustrated.

HTH
--
Kevin James.
Tua'r Goleuni


I found the following on a web site.

********************************************************************************************************

For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula

=DATE(YEAR(A1),MONTH(A1)+6, DAY(A1)+10)

gives 3/7/98.

*********************************************************************************************************

I insert the formula and 8/25/97 in a worksheet and sure enough Excel gives me
3/7/98. I understand that Excel adds the months *first* before it adds the
days. Evaluate formula confirms this.

However, I get a different result when I break up the above process into two
and change the sequence of addition. This is what I have done.

I enter 8/25/97 into A1. Then I enter into B1 this formula

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+10)

Please note that I only add 10 days. The result in B1 is 9/4/1997.

Then in C1, I enter

=DATE(YEAR(B1),MONTH(B1)+6,DAY(B1))

After adding six months, I get 3/4/1998 in C1.

As you will have noted, this result is different from 3/7/98 as stated above.
In both Excel processes, I add six months and 10 days to 8/25/97. But the
results are so different depending on the sequence of addition. (I do know
that if I split the process into two and add months *first* and then days,
I'll get 3/7/1998).

If I am not using Excel, I am inclined to add days before I add months as I
want to go with the smallest unit first. Is it just me? What is the habit of
others? In an office environment, if someone wants to add six months and 10
days, which of the above result is really expected?

Comments welcome.

Epinn

 

[Epinn]

<< It depends on which month ......

This basic is very much understood.

Which month is in turn dependent on whether one adds months first or one adds days first. This is the essence of my findings.

I am not sure if I am truly understood.

Epinn

Hi Eppinn,

It depends on which month and therefore "What is a month"?

Sometimes its 28 days, 29 days , 30 days and others it is 31.

Depends on the particular month just what effect adding 10 days
will have on the actual day value, as you illustrated.

HTH
--
Kevin James.
Tua'r Goleuni


I found the following on a web site.

********************************************************************************************************

For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula

=DATE(YEAR(A1),MONTH(A1)+6, DAY(A1)+10)

gives 3/7/98.

*********************************************************************************************************

I insert the formula and 8/25/97 in a worksheet and sure enough Excel gives me
3/7/98. I understand that Excel adds the months *first* before it adds the
days. Evaluate formula confirms this.

However, I get a different result when I break up the above process into two
and change the sequence of addition. This is what I have done.

I enter 8/25/97 into A1. Then I enter into B1 this formula

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+10)

Please note that I only add 10 days. The result in B1 is 9/4/1997.

Then in C1, I enter

=DATE(YEAR(B1),MONTH(B1)+6,DAY(B1))

After adding six months, I get 3/4/1998 in C1.

As you will have noted, this result is different from 3/7/98 as stated above.
In both Excel processes, I add six months and 10 days to 8/25/97. But the
results are so different depending on the sequence of addition. (I do know
that if I split the process into two and add months *first* and then days,
I'll get 3/7/1998).

If I am not using Excel, I am inclined to add days before I add months as I
want to go with the smallest unit first. Is it just me? What is the habit of
others? In an office environment, if someone wants to add six months and 10
days, which of the above result is really expected?

Comments welcome.

Epinn

 

[Bob Phillips]

Excel will evaluate each part as it encounters it, so the 6 gets added to
the month first, then the days. Because adding 6 to the month takes you into
Feb, the 28 day month, adding 10 days takes you further into March.

To do it your way, try

=DATE(YEAR(DATE(YEAR(A1),MONTH(A1),DAY(A1)+10)),MONTH(DATE(YEAR(A1),MONTH(A1
),DAY(A1)+10))+6,DAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)+10)))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I found the following on a web site.

****************************************************************************
****************************

For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula

=DATE(YEAR(A1),MONTH(A1)+6, DAY(A1)+10)

gives 3/7/98.

****************************************************************************
*****************************

I insert the formula and 8/25/97 in a worksheet and sure enough Excel gives
me 3/7/98. I understand that Excel adds the months *first* before it adds
the days. Evaluate formula confirms this.

However, I get a different result when I break up the above process into two
and change the sequence of addition. This is what I have done.

I enter 8/25/97 into A1. Then I enter into B1 this formula

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+10)

Please note that I only add 10 days. The result in B1 is 9/4/1997.

Then in C1, I enter

=DATE(YEAR(B1),MONTH(B1)+6,DAY(B1))

After adding six months, I get 3/4/1998 in C1.

As you will have noted, this result is different from 3/7/98 as stated
above. In both Excel processes, I add six months and 10 days to 8/25/97.
But the results are so different depending on the sequence of addition. (I
do know that if I split the process into two and add months *first* and then
days, I'll get 3/7/1998).

If I am not using Excel, I am inclined to add days before I add months as I
want to go with the smallest unit first. Is it just me? What is the habit
of others? In an office environment, if someone wants to add six months and
10 days, which of the above result is really expected?

Comments welcome.

Epinn

 

[excelent]

Hi Epinn
Interesting discover u got there, havent thort about that before
If i got ur right - u are questening is Microsoft Excel doing it the right
way?
adding month before days,- hmm who nows - im not sure but the result
sure depending on that. And im sure alot of pepole would do it the 1 way
and som the other.
Therefor we have to question our self every time what result do we want.

sry. my frensh - Denmark u no



"Epinn" skrev:

 

[Tom Ogilvy]

No. If you add days so the sum of the original days and the added days is
less than or equal to a sum of 28, then there is no difference.

If you add days to exceed a sum of 28, then you are dealing with the
irregular quantity of days in a month. You say you already understand this,
so there is no reason to belabor it.

There is no common expectation. It would depend on the context of the
business rules in which it is used. The main point is that as a general
concept, it is imprecise - so what is correct must be spelled out within the
context of the usage.

--
Regards,
Tom Ogilvy





<< It depends on which month ......

This basic is very much understood.

Which month is in turn dependent on whether one adds months first or one
adds days first. This is the essence of my findings.

I am not sure if I am truly understood.

Epinn

Hi Eppinn,

It depends on which month and therefore "What is a month"?

Sometimes its 28 days, 29 days , 30 days and others it is 31.

Depends on the particular month just what effect adding 10 days
will have on the actual day value, as you illustrated.

HTH
--
Kevin James.
Tua'r Goleuni


I found the following on a web site.

********************************************************************************************************

For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula

=DATE(YEAR(A1),MONTH(A1)+6, DAY(A1)+10)

gives 3/7/98.

*********************************************************************************************************

I insert the formula and 8/25/97 in a worksheet and sure enough Excel gives
me
3/7/98. I understand that Excel adds the months *first* before it adds the
days. Evaluate formula confirms this.

However, I get a different result when I break up the above process into two
and change the sequence of addition. This is what I have done.

I enter 8/25/97 into A1. Then I enter into B1 this formula

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+10)

Please note that I only add 10 days. The result in B1 is 9/4/1997.

Then in C1, I enter

=DATE(YEAR(B1),MONTH(B1)+6,DAY(B1))

After adding six months, I get 3/4/1998 in C1.

As you will have noted, this result is different from 3/7/98 as stated
above.
In both Excel processes, I add six months and 10 days to 8/25/97. But the
results are so different depending on the sequence of addition. (I do know
that if I split the process into two and add months *first* and then days,
I'll get 3/7/1998).

If I am not using Excel, I am inclined to add days before I add months as I
want to go with the smallest unit first. Is it just me? What is the habit
of
others? In an office environment, if someone wants to add six months and 10
days, which of the above result is really expected?

Comments welcome.

Epinn

 

[Daniel.M]

Hi Bob, Epinn,

Or
=DATE(YEAR(A1+10),MONTH(A1+10)+6,DAY(A1+10))

As A1+10 can replace DATE(YEAR(A1),MONTH(A1),DAY(A1)+10)

Regards,

Daniel M.

 

[Epinn]

Thanks all for the input.

I find the following interesting. Does "0" day do the trick? Can someone clarify please?
*******************************************************************

=DATE(YEAR(A1),MONTH(A1)+2,0)

For example, if A1 contains the date 31-Jan-2002, the above formula will return 28-Feb-2002.

**********************************************************************************************************

Epinn


I found the following on a web site.

********************************************************************************************************

For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula

=DATE(YEAR(A1),MONTH(A1)+6, DAY(A1)+10)

gives 3/7/98.

*********************************************************************************************************

I insert the formula and 8/25/97 in a worksheet and sure enough Excel gives me 3/7/98. I understand that Excel adds the months *first* before it adds the days. Evaluate formula confirms this.

However, I get a different result when I break up the above process into two and change the sequence of addition. This is what I have done.

I enter 8/25/97 into A1. Then I enter into B1 this formula

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+10)

Please note that I only add 10 days. The result in B1 is 9/4/1997.

Then in C1, I enter

=DATE(YEAR(B1),MONTH(B1)+6,DAY(B1))

After adding six months, I get 3/4/1998 in C1.

As you will have noted, this result is different from 3/7/98 as stated above. In both Excel processes, I add six months and 10 days to 8/25/97. But the results are so different depending on the sequence of addition. (I do know that if I split the process into two and add months *first* and then days, I'll get 3/7/1998).

If I am not using Excel, I am inclined to add days before I add months as I want to go with the smallest unit first. Is it just me? What is the habit of others? In an office environment, if someone wants to add six months and 10 days, which of the above result is really expected?

Comments welcome.

Epinn

 

[Bob Phillips]

Clarify what exactly, the 0 value?

Excel has a (pleasant) habit of working out the nth day of a date. So a
formula of

=DATE(2006,10,n)

will find the nth day of October. The great part is that n can be any
positive number, greater than 31 even, and Excel will calculate it (for
instance, the 62nd day of October for Excel is 1st Dec).

But even better, n can be 0 or even negative, so the -62nd day of October is
30th July.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Thanks all for the input.

I find the following interesting. Does "0" day do the trick? Can someone
clarify please?
*******************************************************************

=DATE(YEAR(A1),MONTH(A1)+2,0)

For example, if A1 contains the date 31-Jan-2002, the above formula will
return 28-Feb-2002.

****************************************************************************
******************************

Epinn

<<
I found the following on a web site.

****************************************************************************
****************************

For example, adding 6 months and 10 days to 8/25/97, in A1 with the formula

=DATE(YEAR(A1),MONTH(A1)+6, DAY(A1)+10)

gives 3/7/98.

****************************************************************************
*****************************

I insert the formula and 8/25/97 in a worksheet and sure enough Excel gives
me 3/7/98. I understand that Excel adds the months *first* before it adds
the days. Evaluate formula confirms this.

However, I get a different result when I break up the above process into two
and change the sequence of addition. This is what I have done.

I enter 8/25/97 into A1. Then I enter into B1 this formula

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+10)

Please note that I only add 10 days. The result in B1 is 9/4/1997.

Then in C1, I enter

=DATE(YEAR(B1),MONTH(B1)+6,DAY(B1))

After adding six months, I get 3/4/1998 in C1.

As you will have noted, this result is different from 3/7/98 as stated
above. In both Excel processes, I add six months and 10 days to 8/25/97.
But the results are so different depending on the sequence of addition. (I
do know that if I split the process into two and add months *first* and then
days, I'll get 3/7/1998).

If I am not using Excel, I am inclined to add days before I add months as I
want to go with the smallest unit first. Is it just me? What is the habit
of others? In an office environment, if someone wants to add six months and
10 days, which of the above result is really expected?

Comments welcome.

Epinn