# Calculate Week Start Date

#### geliseev

##### Board Regular
Hi All,

I am trying to calculate the week start date (first day of the week is Monday) in a set of dates.

For example,

for 7/1/2011 (Friday) the week start date will be 6/27/2011 (Monday).
for 7/6/2011 (Wednesday) the week start date will be 7/4/2011 (Monday)
for 7/26/2011 (Tuesday) the week start date will be 7/25/2011 (Monday).

How do you implement this with a formula?

Thanks!

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### Jonmo1

##### MrExcel MVP
Try

=A1-WEEKDAY(A1,3)

#### T. Valko

##### Well-known Member
Hi All,

I am trying to calculate the week start date (first day of the week is Monday) in a set of dates.

For example,

for 7/1/2011 (Friday) the week start date will be 6/27/2011 (Monday).
for 7/6/2011 (Wednesday) the week start date will be 7/4/2011 (Monday)
for 7/26/2011 (Tuesday) the week start date will be 7/25/2011 (Monday).

How do you implement this with a formula?

Thanks!
Try this...

A1 = some date

=A1-WEEKDAY(A1,2)+1

Awesome, thanks

Or

=A2-MOD(A2-2,7)

#### T. Valko

##### Well-known Member
Or

=A2-MOD(A2-2,7)
Only if you're using the default 1900 date system. If you're using the 1904 date system then that formula will be off by one day.

Awesome, thanks
You're welcome!

#### noriebhe_regis

##### New Member
Thanks for your help T. Valko.

I used your formula and it works!
It was cool and awesome.

Anyway, I'm sorry to ask u this.

Can you please explain further to us the formula.

A1 = some date

=A1-WEEKDAY(A1,2)+1

I just wanted to understand the formula. I don't get it...too bad.

Appreciate your help.

Thanx!
Bhe

Last edited:

#### crazyjay32

##### New Member
Can you please explain further to us the formula.

Sure! Happy to try anyway!

The formula A1-WEEKDAY(A1,3) is simply a date calculation.

For example, if you you have today's date (11/13/15) in A1, and in B1 you have =A1-4, the cell returns the date minus 4 days: 11/6/15.

So now that we've established that simply subtracting whole numbers from a date will give you that many days back from the given date, the suggested answer now makes more sense.

The WEEKDAY function takes in 2 input parameters, the date, then "return_type" which according to the tooltip that comes up has several options: 1,2,3 or 11 thru 17. It returns a whole number representing the day of the week, based on the "return_type" you specify. So, for example, WEEKDAY(A1,3) returns the weekday number based on return_type "3", which starts on Monday (0), ends on Sunday (6). Since 11/13/15 is a Friday, and return_type "3" starts counting from 0 on Monday, WEEKDAY(A1,3) = 4.

Continuing on with 11/13/15 as the day we're evaluating, =A1 - WEEKDAY(A1,3) would mean 11/13/15 - 4 = 11/9/15.

So depending on what day of the week you want your week start date to be, you would need to use the WEEKDAY function accordingly.

HTH!

Threads
1,095,372
Messages
5,444,067
Members
405,265
Latest member
Iram