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



"Conan Kelly" wrote:

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






"Dermot" wrote in message
...
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



"Conan Kelly" wrote:

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





"Dermot" wrote in message
...
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" wrote:

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





"Dermot" wrote in message
...
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