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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
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:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top