alternative for EDATE

K

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
 
R

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.
Click to expand...

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
 
F

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

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
 
R

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
Click to expand...

You're welcome. Glad to help.


--ron
 
Top