alternative for EDATE

  • Thread starter Thread starter Karin Stiles
  • Start date Start date
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
 
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
 
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))))
 
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
 
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
 
Back
Top