# Calculate future date to nearest Monday

1. ## 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.

2. ## Re: Caclulate future date to nearest Monday

Hi, an attempt.

+1 Month

``Please Login or Register  to view this content.``
+1 Year:

``Please Login or Register  to view this content.``

Regards

3. ## Re: Caclulate future date to nearest Monday

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.

4. ## Re: Caclulate future date to nearest Monday

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.`

5. ## Re: Caclulate future date to nearest Monday

Thank you all for your assistance. All of these suggestions appear to work for my needs.

6. ## Re: Calculate future date to nearest Monday

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"

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1