Calculate Week Start Date

geliseev

Board Regular
Joined
Feb 3, 2009
Messages
79
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!
 

Some videos you may like

Excel Facts

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

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Or

=A2-MOD(A2-2,7)
 

noriebhe_regis

New Member
Joined
Mar 22, 2015
Messages
1
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.:eek:

Appreciate your help. :)

Thanx!
Bhe
 
Last edited:

crazyjay32

New Member
Joined
Nov 13, 2015
Messages
1
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!
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top