Needing formula for date 4 weeks ago on monday

**Aroy2416** · 11-14-2013, 08:44 AM

Hello,

I'm a AIT Instructor and i need a formula for specific date to be 4 weeks in the past and land on a Monday. so if it is 27 Nov 13 the value date needs to be 28 Oct 13. The students have to take a test 4 weeks before graduation and it has to be on a Monday.

Thanks

V/R
SGT Roy, Adam

**benishiryo** · 11-14-2013, 08:55 AM

welcome to the forum, Adam. try:
=A1-7*4-WEEKDAY(A1-7*4,2)+1

**Aroy2416** · 11-14-2013, 09:00 AM

Thank you
can you please explain each value so i can understand and let my battle buddies know also.

V/R
SGT Roy

**benishiryo** · 11-14-2013, 09:25 AM

glad to help.
7*4
gives is 7 days * 4 weeks

so A1-7*4 gives you the date 4 weeks ago.

WEEKDAY(A1-7*4,2)
the WEEKDAY formula returns the day number of any given date. the first argument, as like any formulas, is mandatory. it's the date. and in here, we'll input the A1-7*4. the 2nd argument is optional, and if unfilled, is default as 1. that returns 1 if the date is a Sunday, all the way to 7 if it's a Sat. i used 2. and that returns 1 if the date is a Monday, all the way to 7 if it's a Sun.

so with that, WEEKDAY of the 30 Oct 13 is 3, a Wed. 30 Oct 13 minus 3 is a Sunday. Sunday + 1 is a Monday. so you'll always get a Monday. hope that helps