EDate formula

[mmurphy1360]

I am working with the EDATE formula that I found here on the forum and it has been very helpful in calculating the Monthly, Quarterly and Annual dates needed in a spreadsheet that I am working on.

=IF(J32="Annually",EDATE(H32,12),IF(J32="Quarterly",EDATE(H32,3),IF(J32="Monthly",EDATE(H32,1),"")))

Note: I know to change each number to correspond to the row the formula falls in. I just don't know how or where to add "Weekly" to the formula.

However, I have run into some invoices that are now weekly. What would be the formula to add in a weekly calculation? I am also having a problem updating the dropdown so that I can add Weekly. Right now it only has the options to select monthly, quarterly or Annually. I know I created the list in a separate spread sheet and Defined the Name/ List but it will not let me go back and edit the list or add "Weekly" to it. Need help.

Thanks,
Michelle

[Tony Valko]

For weekly, maybe this...

=IF(J32="Weekly",H32+7...

For your drop down list...

Goto the Name manager and see how List is defined.

[loginjmor]

Hi -

EDATE doesn't have a weekly option, just number of months after the start date, so Annual is 12 months, quarterly is 3 months, and monthly is 1 month as you have in your formula. To add 1 week to the date stored in H32, simply add an option for "Weekly" and add 7 to H32. I have modified your formula as follows:


=IF(J32="Annually",EDATE(H32,12),IF(J32="Quarterly",EDATE(H32,3),IF(J32="Monthly",EDATE(H32,1),IF(J32="Weekly",H32+7,""))))

As to why your dropdown list won't update, check the cell references. Otherwise, you will need to post part of your spreadsheet (omitting sensitive data) to the forum so we can look at it.

Hope this helps.

[mmurphy1360]

Thank you all for your help. I did finally get the drop down figured out and the formula you gave for "Weekly" did work!! Your the best, going to love being part of this forum for helpful tips.

Michelle

[loginjmor]

Great! If you're satisfied with the result, please edit the thread title to add the word SOLVED per the instructions below. Glad we could help! Welcome to the forum!

[mmurphy1360]

EDate and drop-down menu problems solved, thank you loginjmor and Tony Valko!

[Tony Valko]

You're welcome. We appreciate the feedback!