Calculate future date to nearest Monday

Hello everyone. I am brand new to the forum and I think my issue is a doozy. I have tried searching, but have not found this particular issue addressed.

I am trying to build an Excel Spreadsheet that will calculate the return date of backup tapes from offsite storage. We have three classifications of tapes: Weekly, Monthly and Yearly. Weekly tapes are offsite for one month, Monthly tapes are offsite for one year and Yearly tapes are offsite for seven years. Tapes are picked up and delivered on Mondays (unless Monday is a holiday).

If I enter an offsite date of a weekly tape as 10/14/2013, I need a formula that would add one month and then "round" it to the nearest Monday date.

If I enter an offsite date of a Monthly tape as 9/30/2013, I need a formula that would add one year and then "round" to the first Monday of the resulting month.

I believe if I can get these two formulas nailed down, I can work out the rest. Any thoughts or suggestions would be greatly appreciated.

Hi, an attempt.

+1 Month

Please Login or Register  to view this content.

+1 Year:

Please Login or Register  to view this content.

Please triple-check the formula results

Regards

Formula for Weekly tape:

Please Login or Register  to view this content.

Formula for Monthly tape:

Please Login or Register  to view this content.

I hope this will resolve your requirement.

Hi mswauger, Welcome to the forum

here's another approach for monthly (given the date is in cell A2):

Formula:

Please Login or Register  to view this content.

Note - for tue and wed it would go back to previous monday and for thursday to sunday it would select the next monday.

yearly formula:

Formula:

Please Login or Register  to view this content.

Assuming you are using default 1900 date system this formula will add one year then give you the first Monday of that month

=FLOOR(EOMONTH(A1,11)+5,7)+2

This one adds a month and rounds to the nearest Monday

=FLOOR(EDATE(A1,1)+1,7)+2

You can include all 3 options in a formula like this

=IF(B1="Weekly",FLOOR(EDATE(A1,1)+1,7),FLOOR(EOMONTH(A1,IF(B1="Monthly",11,83))+5,7))+2

Assuming B1 includes only the text "Weekly", "Monthly" or "Yearly"