+ Reply to Thread
Results 1 to 6 of 6

Calculate future date to nearest Monday

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    Harrisburg, PA
    MS-Off Ver
    Excel 2007
    Posts
    2

    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.
    Last edited by mswauger; 10-09-2013 at 02:22 PM.

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    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.

    Please triple-check the formula results

    Regards
    Attached Files Attached Files
    Last edited by canapone; 10-09-2013 at 10:02 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    10-09-2013
    Location
    New Delhi
    MS-Off Ver
    Excel 2010
    Posts
    6

    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.
    Last edited by pranavshandilya; 10-09-2013 at 10:10 AM. Reason: Correcting Code

  4. #4
    Forum Contributor Bhuvi's Avatar
    Join Date
    04-19-2013
    Location
    Delhi, India
    MS-Off Ver
    MS Excel 2003,07,10
    Posts
    153

    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: copy to clipboard
    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: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Bhuvi; 10-09-2013 at 10:34 AM. Reason: yearly formula included
    If this helped and you wish to say thanks, then Please click on the Star* icon below this post.

  5. #5
    Registered User
    Join Date
    10-09-2013
    Location
    Harrisburg, PA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Caclulate future date to nearest Monday

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

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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"
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Display Fridays date (and message) if current date is Monday
    By Pierce Quality in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-26-2013, 09:40 AM
  2. [SOLVED] Find monday preceeding given-date unless date is a monday
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2012, 07:42 AM
  3. [SOLVED] Compare strings for match in separate worksheet and return nearest future date
    By kungfood in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2012, 05:19 PM
  4. Nearest Monday
    By luvthavodka in forum Excel General
    Replies: 5
    Last Post: 07-21-2010, 06:13 PM
  5. Replies: 7
    Last Post: 11-16-2008, 05:48 PM

Bookmarks

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