+ Reply to Thread
Results 1 to 6 of 6

Nearest Monday

  1. #1
    Registered User
    Join Date
    05-26-2006
    Posts
    20

    Nearest Monday

    For example: If i am creating a project timeline, and I am waiting for an approval from a client. My next step in the time line will take place 6 workdays following the date of approval, but 6 workdays, then the next Monday.

    If the client submits approval on Tuesday, Jan 10th, then 6 workdays will fall on Wednesday the 18, the next Monday will be the 23rd.

    Any ideas on a function to do this?
    Last edited by luvthavodka; 07-21-2010 at 05:54 PM.

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: In Excel, how would I force a date to land on the nearest Monday?

    try this out

    =IF(ROUND(--("0."&WEEKDAY(A1,2)),0),A1+(7-WEEKDAY(A1,2)+1),A1-(WEEKDAY(A1,2)-1))

    Assuming that date stored in A1. Format output as ddd dd mmm yyyy
    HTH
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, «Born in USSR»
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: In Excel, how would I force a date to land on the nearest Monday?

    Forget that.... I missed this part - "then 6 workdays will fall on Wednesday the 18"

  4. #4
    Registered User
    Join Date
    05-26-2006
    Posts
    20

    Re: In Excel, how would I force a date to land on the nearest Monday?

    almost, but using the example of 22/07/2010, it returns 19/07/2010... which is the previous Monday and ignores the fact it should be 6 days, then the next Monday.

    I've tried =IF(WEEKDAY(A1+6,1)=2,A1+6,A1+6+(7-WEEKDAY(A1+6)+2)), and this works

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

    Re: In Excel, how would I force a date to land on the nearest Monday?

    That doesn't add 6 workdays, though (unless every day is a workday?) try

    =WORKDAY(A1,6)+7-WEEKDAY(WORKDAY(A1,6)-2)
    Audere est facere

  6. #6
    Registered User
    Join Date
    05-26-2006
    Posts
    20

    Re: In Excel, how would I force a date to land on the nearest Monday?

    Good call daddylonglegs - hadn't considered that. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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