Nearest Monday

**luvthavodka** · 07-21-2010, 05:22 PM

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?

**contaminated** · 07-21-2010, 05:46 PM

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

**contaminated** · 07-21-2010, 05:49 PM

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

**luvthavodka** · 07-21-2010, 05:49 PM

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