edate 1 March!!

[Yangas]

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 31s
of a month

 

[Ron Rosenfeld]

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.

 

[Yangas]

ok
I'm trying to get column a to display todays day, but the month t
increase by one all the way down. I need a formula to do this (i
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 retur
-1day

thanks in advance

;1604208']On Tue, 31 Jul 2012 06:51:12 +0000, Yanga

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 yo
see, and the results you would like.
EDATE is designed so as to not go past the last day of the month

 

[Stunn]

Try this:

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

 

[Ron Rosenfeld]

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}))