alternative for EDATE

[Karin Stiles]

Hi everyone--

Originally I used EDATE in a formula, but it only works if the Analysis
Toolpak is installed. Is there another formula I could use instead? I'm
sending this spreadsheet to other people and I don't want them to have to
install the Analysis Toolpak.

The formula is
=IF(G26="","",EDATE($H$25,G26))

where H25 has the start date, and col G has numbers of months.

I can't just do something like
=IF(G26="","",$H$25+G26*30)
because not every month has 30 days.

The important thing is that all subsequent cells fall on the same day as
whatever is in H25.

Thanks for any ideas,
Karin

 

[Ron Rosenfeld]

Hi everyone--

Originally I used EDATE in a formula, but it only works if the Analysis
Toolpak is installed. Is there another formula I could use instead? I'm
sending this spreadsheet to other people and I don't want them to have to
install the Analysis Toolpak.

The formula is
=IF(G26="","",EDATE($H$25,G26))

where H25 has the start date, and col G has numbers of months.

I can't just do something like
=IF(G26="","",$H$25+G26*30)
because not every month has 30 days.

The important thing is that all subsequent cells fall on the same day as
whatever is in H25.

EDATE does not always do this. It adjusts for the end of the month. For
example, if H25 is 31 Jan 2004 and G26 contains a '1', then your formula will
return 2/29/2004.

To mimic the performance of EDATE, I believe the following will work:

=MIN(DATE(YEAR($H$25),MONTH($H$25)+G26,DAY($H$25)),
DATE(YEAR($H$25),MONTH($H$25)+G26+1,0))


--ron

 

[Frank Kabel]

Hi
try:
=DATE(YEAR(start_date),MONTH(start_date)+months,MIN(DAY(start_date),DAY
(DATE(YEAR(start_date),MONTH(start_date)+months+1,0))))

 

[Karin Stiles]

Thanks to both of you, Ron and Frank. Both your answers work great! I
NEVER would have come up with anything like that... Thanks again! : )
-Karin

 

[Ron Rosenfeld]

Thanks to both of you, Ron and Frank. Both your answers work great! I
NEVER would have come up with anything like that... Thanks again! : )
-Karin

You're welcome. Glad to help.


--ron