Hi I'm using =edate(a1,1) a1 being =date(year(x), month(x),day(today()) then =edate(a2,1) then =edate(a3,1) etc
and I get problems with any day after 28 - thanks Feb!!

Is there anyway to get this displaying correctly for days such as 31st of a month?

What is it you're actually trying to show?

Which version of Excel are you using?

On Tue, 31 Jul 2012 06:51:12 +0000, Yangas wrote:


Hi I'm using =edate(a1,1) a1 being =date(year(x), month(x),day(today())
then =edate(a2,1) then =edate(a3,1) etc
and I get problems with any day after 28 - thanks Feb!!

Is there anyway to get this displaying correctly for days such as 31st
of a month?

What problem are you having?

Please post some dates that are producing a "problem", the results you see, and the results you would like.
EDATE is designed so as to not go past the last day of the month.

ok
I'm trying to get column a to display todays day, but the month to increase by one all the way down. I need a formula to do this (in xl2003) recognising days with 31 days and February.

=IF((MONTH(A226)+1)=2,DATE(YEAR(A226),MONTH(A226)+ 1,DAY(IF(DAY(TODAY())28,28,TODAY()))),DATE(YEAR(A 226),MONTH(A226)+1,DAY(TODAY()))) doesn't seem to work on the 31st of each month i get #N/A
nor
=IF(EOMONTH(A228,0),EOMONTH(A228,1),EDATE(A228,1)) this seems to return -1day

thanks in advance

Try this:

=EDATE(A$1,ROWS(A$1:A1))

On Wed, 1 Aug 2012 02:23:48 +0000, Yangas wrote:


ok
I'm trying to get column a to display todays day, but the month to
increase by one all the way down. I need a formula to do this (in
xl2003) recognising days with 31 days and February.

=IF((MONTH(A226)+1)=2,DATE(YEAR(A226),MONTH(A226) +1,DAY(IF(DAY(TODAY())28,28,TODAY()))),DATE(YEAR( A226),MONTH(A226)+1,DAY(TODAY())))
doesn't seem to work on the 31st of each month i get #N/A
nor
=IF(EOMONTH(A228,0),EOMONTH(A228,1),EDATE(A228,1) ) this seems to return
-1day

thanks in advance



The key is to make everything relative to the first row in which you have your initial date; and not to the previous row.
For example:

=edate($A$1,ROWS($1:1))

or, if you do not want to have the ATP installed in your Excel 2003:

=MIN(DATE(YEAR($A$1),MONTH($A$1)+ROWS($1:1)+{1,0}, DAY($A$1)*{0,1}))