Copy Down A formula advice......

[Guest]

I create this formula and copied it down.

=DATE(YEAR(B5), MONTH(B5)+1, DAY(B5))

I have custom formatted the date: mm/yy

It works fine for most month sequences but when I enter an end of month date
like 31/01/2005 and copy it down....February is missing...

Mar-05
Apr-05
May-05

Can any one explain what I am overlooking?

Thanks in advance

 

[Niek Otten]

Format as mm/dd/yy and you'll see what's going wrong

You'll have to think about what you mean by "same day" for a month where that day doesn't exist.
Once you know, post again. There is always a solution.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I create this formula and copied it down.
|
| =DATE(YEAR(B5), MONTH(B5)+1, DAY(B5))
|
| I have custom formatted the date: mm/yy
|
| It works fine for most month sequences but when I enter an end of month date
| like 31/01/2005 and copy it down....February is missing...
|
| Mar-05
| Apr-05
| May-05
|
| Can any one explain what I am overlooking?
|
| Thanks in advance
|
|

 

[Pete_UK]

You are taking the same day as in your original date, and if this is
31 then there is no date 31st February 2005 - in this case, Excel will
wrap the number around and make it 3rd March 2005, and thereafter it
will be the 3rd of each month - adjust your formatting to dd/mm/yy to
see.

You might like to change your formula to:

=DATE(YEAR(B5), MONTH(B5)+1, 1)

so you will always get the 1st day of the next month.

Hope this helps.

Pete

 

[Guest]

Hi Pete
Thanks for your explanation.

Please clarify my understanding....by formatting mm/yy.....excel assume all
months have the same number of days....hence when the a month doesn't have a
particular day .......that month is skipped.

Further Questions
1. In your formula: =DATE(YEAR(B5), MONTH(B5)+1, 1)
What does the , 1 after +1

2. What formula could be used to generate the sequence from any day in a
calendar month. IE. 31/1/2005 to 28/2/2005 to take into account that months
have different numbers of days?

Thanks in advance

 

[Conan Kelly]

Dermot,

If you ALWAYS want the last day of the next month, use this formula:

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

I use this formula very often.

Also, lookup the DATE() function in help for more info. The 3 arguments
(year, month, & day) can be negative or positive, and they can be above &
beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31 for
days). For example:

(all dates in USA date format--m/d/yyyy)
=DATE(2007,0,1) would return 12/1/2006
=DATE(2007,-1,1) would return 11/1/2006
=DATE(2007,13,1) would return 1/1/2008
=DATE(2007,14,1) would return 2/1/2008
=DATE(2007,1,95) would return 4/5/2007

HTH,

Conan Kelly

 

[Conan Kelly]

Dermot,

check out my response to your original post for info that might answer these
questions.

HTH,

Conan Kelly

 

[Guest]

Hi Conan
Thanks for the great explanation and examples.
We must have cross posted as I missed your reply

I have read quite a bit about dates ...it's like opening can of worms.
I didn' t realise my error until today when I entered 31st of the month...I
assume that although I custom formatted the date as mm/yy I thought I could
still enter a full date dd/mm/yy and get for example Oct 05...I was wrong!

I will investiage your examples thanks very much for posting.

 

[Conan Kelly]

Dermot,

I didn' t realise my error until today when I entered 31st of the
month...I
assume that although I custom formatted the date as mm/yy I thought I
could
still enter a full date dd/mm/yy and get for example Oct 05...I was wrong!

You *should* be able to do that.

If you have your cells formatted as "mm/yy", then if you enter "31/10/05",
then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy", then
entering "31/10/05" SHOULD display "Oct 05".

Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of
the cell is still going to be "31/10/05", and "31/10/05" is the value that
will be used in any calculations that refer to this cell.

Also, the arguments for DATE() are Year, Month, and Day (in that order):

=DATE([Year],[Month],[Day])

So:

=DATE(2007,11,21) will return 21/11/2007
=DATE(2005,10,31) will return 31/10/2005
=DATE(2006,5,1) will return 1/5/2006

Also, each argument can be negative, positive, calculated, greater than/less
than expected range, etc...

HTH,

Conan

 

[Guest]

Thanks for the reply Conan
It was when I copied 31/01/2005 date down an skipped February Jan 05,
Mar05 etc....

To Quote You
Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of
the cell is still going to be "31/10/05", and "31/10/05" is the value that
will be used in any calculations that refer to this cell.

Question
So if the value reflects the day.......why did it skip the month of February
when copied down.

Please advise a little further..

Thanks in advance

Dermot,

I didn' t realise my error until today when I entered 31st of the
month...I
assume that although I custom formatted the date as mm/yy I thought I
could
still enter a full date dd/mm/yy and get for example Oct 05...I was wrong!

You *should* be able to do that.

If you have your cells formatted as "mm/yy", then if you enter "31/10/05",
then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy", then
entering "31/10/05" SHOULD display "Oct 05".

Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of
the cell is still going to be "31/10/05", and "31/10/05" is the value that
will be used in any calculations that refer to this cell.

Also, the arguments for DATE() are Year, Month, and Day (in that order):

=DATE([Year],[Month],[Day])

So:

=DATE(2007,11,21) will return 21/11/2007
=DATE(2005,10,31) will return 31/10/2005
=DATE(2006,5,1) will return 1/5/2006

Also, each argument can be negative, positive, calculated, greater than/less
than expected range, etc...

HTH,

Conan

Hi Conan
Thanks for the great explanation and examples.
We must have cross posted as I missed your reply

I have read quite a bit about dates ...it's like opening can of worms.
I didn' t realise my error until today when I entered 31st of the
month...I
assume that although I custom formatted the date as mm/yy I thought I
could
still enter a full date dd/mm/yy and get for example Oct 05...I was wrong!


I will investiage your examples thanks very much for posting.

 

[Pete_UK]

I explained that earlier - if you start with 31/01/05 and increment
the month, there is no date of 31st February. What Excel does is to
wrap this around and make it 03/03/05 (i.e. 3 more days after 28th
Feb), which only displays as Mar 05. All subsequent increments of the
month will be correct, as it will take them as being the 3rd of the
month. Thus, February is not shown.

Hope this helps.

Pete

Thanks for the reply Conan
It was when I copied 31/01/2005 date down an skipped February Jan 05,
Mar05 etc....

To Quote You
Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of
the cell is still going to be "31/10/05", and "31/10/05" is the value that
will be used in any calculations that refer to this cell.

Question
So if the value reflects the day.......why did it skip the month of February
when copied down.

Please advise a little further..

Thanks in advance

Dermot,

You *should* be able to do that.

If you have your cells formatted as "mm/yy", then if you enter "31/10/05",
then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy", then
entering "31/10/05" SHOULD display "Oct 05".

Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of
the cell is still going to be "31/10/05", and "31/10/05" is the value that
will be used in any calculations that refer to this cell.

Also, the arguments for DATE() are Year, Month, and Day (in that order):

=DATE([Year],[Month],[Day])

So:

=DATE(2007,11,21) will return 21/11/2007
=DATE(2005,10,31) will return 31/10/2005
=DATE(2006,5,1) will return 1/5/2006

Also, each argument can be negative, positive, calculated, greater than/less
than expected range, etc...

- Show quoted text -

 

[Conan Kelly]

Dermot,

Pete_UK is correct.

Format your cells as "dd/mm/yyyy" so you can see the actual date that XL is
calculating.

using your example, running the formula "=DATE(YEAR(B5), MONTH(B5)+1,
DAY(B5))" on the date 31/01/2005, this is going to be XL's calculation
process:

=DATE(YEAR(31/01/2005),MONTH(31/01/2005) + 1,DAY(31/01/2005))
=DATE(2005, 1 + 1, 31)
=DATE(2005, 2, 31)
(But there is not 31 days in Feb. XL will do the following.)
=DATE(2005, 2, 28 + 3)
(31 days supplied to the formula - 28 days in Feb = 3 days)
=DATE(2005, 2 + 1, 3)
(the 28 days in Feb was converted to 1 month and added to the month that was
supplied to the formula)
=DATE(2005, 3, 3) which returns "03/03/2005"

Now running the same formula on "03/03/2005" will return "03/04/2005"

your formula is adding 1 to the month, but is leaving the day the same, so:

--31/01/2005 tries to become 31/02/2005
--but there is not 31 days in Feb, so 31/02/2005 will become 03/03/2005
(There is not 31 days in February. But if there were, February 31st would
be 3 days after February 28th. Since there are only 28 days in Feb,
February 31st is 3 days after February 28th. 3 days after Feb 28th is Mar
3rd)

HTH. Please write back if you have any more questions or if my explanation
just confuses you more. Also, look up the DATE() function in XL's help. It
will explain the function, its syntax, arguments and give examples.

Conan

Thanks for the reply Conan
It was when I copied 31/01/2005 date down an skipped February Jan 05,
Mar05 etc....

To Quote You
Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value
of
the cell is still going to be "31/10/05", and "31/10/05" is the value that
will be used in any calculations that refer to this cell.

Question
So if the value reflects the day.......why did it skip the month of
February
when copied down.

Please advise a little further..

Thanks in advance

Dermot,

I didn' t realise my error until today when I entered 31st of the
month...I
assume that although I custom formatted the date as mm/yy I thought I
could
still enter a full date dd/mm/yy and get for example Oct 05...I was
wrong!

You *should* be able to do that.

If you have your cells formatted as "mm/yy", then if you enter
"31/10/05",
then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy",
then
entering "31/10/05" SHOULD display "Oct 05".

Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value
of
the cell is still going to be "31/10/05", and "31/10/05" is the value
that
will be used in any calculations that refer to this cell.

Also, the arguments for DATE() are Year, Month, and Day (in that order):

=DATE([Year],[Month],[Day])

So:

=DATE(2007,11,21) will return 21/11/2007
=DATE(2005,10,31) will return 31/10/2005
=DATE(2006,5,1) will return 1/5/2006

Also, each argument can be negative, positive, calculated, greater
than/less
than expected range, etc...

HTH,

Conan

Hi Conan
Thanks for the great explanation and examples.
We must have cross posted as I missed your reply

I have read quite a bit about dates ...it's like opening can of worms.
I didn' t realise my error until today when I entered 31st of the
month...I
assume that although I custom formatted the date as mm/yy I thought I
could
still enter a full date dd/mm/yy and get for example Oct 05...I was
wrong!


I will investiage your examples thanks very much for posting.



:

Dermot,

If you ALWAYS want the last day of the next month, use this formula:

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

I use this formula very often.

Also, lookup the DATE() function in help for more info. The 3
arguments
(year, month, & day) can be negative or positive, and they can be
above &
beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31
for
days). For example:

(all dates in USA date format--m/d/yyyy)
=DATE(2007,0,1) would return 12/1/2006
=DATE(2007,-1,1) would return 11/1/2006
=DATE(2007,13,1) would return 1/1/2008
=DATE(2007,14,1) would return 2/1/2008
=DATE(2007,1,95) would return 4/5/2007

HTH,

Conan Kelly





I create this formula and copied it down.

=DATE(YEAR(B5), MONTH(B5)+1, DAY(B5))

I have custom formatted the date: mm/yy

It works fine for most month sequences but when I enter an end of
month
date
like 31/01/2005 and copy it down....February is missing...

Mar-05
Apr-05
May-05

Can any one explain what I am overlooking?

Thanks in advance

 

[Guest]

Thanks again Pete for Posting

I explained that earlier - if you start with 31/01/05 and increment
the month, there is no date of 31st February. What Excel does is to
wrap this around and make it 03/03/05 (i.e. 3 more days after 28th
Feb), which only displays as Mar 05. All subsequent increments of the
month will be correct, as it will take them as being the 3rd of the
month. Thus, February is not shown.

Hope this helps.

Pete

Thanks for the reply Conan
It was when I copied 31/01/2005 date down an skipped February Jan 05,
Mar05 etc....

To Quote You
Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of
the cell is still going to be "31/10/05", and "31/10/05" is the value that
will be used in any calculations that refer to this cell.

Question
So if the value reflects the day.......why did it skip the month of February
when copied down.

Please advise a little further..

Thanks in advance

Dermot,

I didn' t realise my error until today when I entered 31st of the
month...I
assume that although I custom formatted the date as mm/yy I thought I
could
still enter a full date dd/mm/yy and get for example Oct 05...I was wrong!

You *should* be able to do that.

If you have your cells formatted as "mm/yy", then if you enter "31/10/05",
then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy", then
entering "31/10/05" SHOULD display "Oct 05".

Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value of
the cell is still going to be "31/10/05", and "31/10/05" is the value that
will be used in any calculations that refer to this cell.

Also, the arguments for DATE() are Year, Month, and Day (in that order):

=DATE([Year],[Month],[Day])

So:

=DATE(2007,11,21) will return 21/11/2007
=DATE(2005,10,31) will return 31/10/2005
=DATE(2006,5,1) will return 1/5/2006

Also, each argument can be negative, positive, calculated, greater than/less
than expected range, etc...


Hi Conan
Thanks for the great explanation and examples.
We must have cross posted as I missed your reply

I have read quite a bit about dates ...it's like opening can of worms.
I didn' t realise my error until today when I entered 31st of the
month...I
assume that although I custom formatted the date as mm/yy I thought I
could
still enter a full date dd/mm/yy and get for example Oct 05...I was wrong!

I will investiage your examples thanks very much for posting.

:

If you ALWAYS want the last day of the next month, use this formula:

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

I use this formula very often.

Also, lookup the DATE() function in help for more info. The 3 arguments
(year, month, & day) can be negative or positive, and they can be above &
beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31 for
days). For example:

(all dates in USA date format--m/d/yyyy)
=DATE(2007,0,1) would return 12/1/2006
=DATE(2007,-1,1) would return 11/1/2006
=DATE(2007,13,1) would return 1/1/2008
=DATE(2007,14,1) would return 2/1/2008
=DATE(2007,1,95) would return 4/5/2007

Conan Kelly

I create this formula and copied it down.

=DATE(YEAR(B5), MONTH(B5)+1, DAY(B5))

I have custom formatted the date: mm/yy

It works fine for most month sequences but when I enter an end of month
date
like 31/01/2005 and copy it down....February is missing...

Can any one explain what I am overlooking?

Thanks in advance- Hide quoted text -

- Show quoted text -

 

[Guest]

Hi Conan
Thanks again for the break down explanation of how Excel does this, what
more could I ask for

Much appreciate you taking the time and patience
Cheers

Dermot

Dermot,

Pete_UK is correct.

Format your cells as "dd/mm/yyyy" so you can see the actual date that XL is
calculating.

using your example, running the formula "=DATE(YEAR(B5), MONTH(B5)+1,
DAY(B5))" on the date 31/01/2005, this is going to be XL's calculation
process:

=DATE(YEAR(31/01/2005),MONTH(31/01/2005) + 1,DAY(31/01/2005))
=DATE(2005, 1 + 1, 31)
=DATE(2005, 2, 31)
(But there is not 31 days in Feb. XL will do the following.)
=DATE(2005, 2, 28 + 3)
(31 days supplied to the formula - 28 days in Feb = 3 days)
=DATE(2005, 2 + 1, 3)
(the 28 days in Feb was converted to 1 month and added to the month that was
supplied to the formula)
=DATE(2005, 3, 3) which returns "03/03/2005"

Now running the same formula on "03/03/2005" will return "03/04/2005"

your formula is adding 1 to the month, but is leaving the day the same, so:

--31/01/2005 tries to become 31/02/2005
--but there is not 31 days in Feb, so 31/02/2005 will become 03/03/2005
(There is not 31 days in February. But if there were, February 31st would
be 3 days after February 28th. Since there are only 28 days in Feb,
February 31st is 3 days after February 28th. 3 days after Feb 28th is Mar
3rd)

HTH. Please write back if you have any more questions or if my explanation
just confuses you more. Also, look up the DATE() function in XL's help. It
will explain the function, its syntax, arguments and give examples.

Conan

Thanks for the reply Conan
It was when I copied 31/01/2005 date down an skipped February Jan 05,
Mar05 etc....

To Quote You
Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value
of
the cell is still going to be "31/10/05", and "31/10/05" is the value that
will be used in any calculations that refer to this cell.

Question
So if the value reflects the day.......why did it skip the month of
February
when copied down.

Please advise a little further..

Thanks in advance

Dermot,

I didn' t realise my error until today when I entered 31st of the
month...I
assume that although I custom formatted the date as mm/yy I thought I
could
still enter a full date dd/mm/yy and get for example Oct 05...I was
wrong!


You *should* be able to do that.

If you have your cells formatted as "mm/yy", then if you enter
"31/10/05",
then "10/05" SHOULD be displayed. If cells are formatted as "mmm yy",
then
entering "31/10/05" SHOULD display "Oct 05".

Keep in mind, "10/05" or "Oct 05" will be DISPLAYED, but the actual value
of
the cell is still going to be "31/10/05", and "31/10/05" is the value
that
will be used in any calculations that refer to this cell.

Also, the arguments for DATE() are Year, Month, and Day (in that order):

=DATE([Year],[Month],[Day])

So:

=DATE(2007,11,21) will return 21/11/2007
=DATE(2005,10,31) will return 31/10/2005
=DATE(2006,5,1) will return 1/5/2006

Also, each argument can be negative, positive, calculated, greater
than/less
than expected range, etc...

HTH,

Conan





Hi Conan
Thanks for the great explanation and examples.
We must have cross posted as I missed your reply

I have read quite a bit about dates ...it's like opening can of worms.
I didn' t realise my error until today when I entered 31st of the
month...I
assume that although I custom formatted the date as mm/yy I thought I
could
still enter a full date dd/mm/yy and get for example Oct 05...I was
wrong!


I will investiage your examples thanks very much for posting.



:

Dermot,

If you ALWAYS want the last day of the next month, use this formula:

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

I use this formula very often.

Also, lookup the DATE() function in help for more info. The 3
arguments
(year, month, & day) can be negative or positive, and they can be
above &
beyond the expected range (1 to 12 for months; 1 to 28, 29, 30, or 31
for
days). For example:

(all dates in USA date format--m/d/yyyy)
=DATE(2007,0,1) would return 12/1/2006
=DATE(2007,-1,1) would return 11/1/2006
=DATE(2007,13,1) would return 1/1/2008
=DATE(2007,14,1) would return 2/1/2008
=DATE(2007,1,95) would return 4/5/2007

HTH,

Conan Kelly





I create this formula and copied it down.

=DATE(YEAR(B5), MONTH(B5)+1, DAY(B5))

I have custom formatted the date: mm/yy

It works fine for most month sequences but when I enter an end of
month
date
like 31/01/2005 and copy it down....February is missing...

Mar-05
Apr-05
May-05

Can any one explain what I am overlooking?

Thanks in advance