Calculate Week Start Date

[geliseev]

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!

 

[Jonmo1]

Try

=A1-WEEKDAY(A1,3)

 

[T. Valko]

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

 

[geliseev]

Awesome, thanks

 

[VoG]

Or

=A2-MOD(A2-2,7)

 

[T. Valko]

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.

 

[T. Valko]

Awesome, thanks

You're welcome!

 

[noriebhe_regis]

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

 

[crazyjay32]

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!