# Nearest Monday

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

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

3. ## 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. ## 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. ## 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)

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

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