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