Weekday to first day of the week

[undemane]

In column B, I have dates. I need to figure out what the first working day in that week was, and populate that date in another column.
For e.g., if I had 1/13/11 in B2, then in C2, I want the date 1/10/11.

I am trying to figure out how many events I had each week. My thought is to do a pivot table to show how many events started on week of 1/10/11, and the weeks thereafter. Is there an easier way to accomplish this?

 

[DonkeyOte]

If you use a Pivot you don't need to normalise the dates - you can Group the Date Field in your Pivot by Day, setting start date as the Monday prior to first record and with an Interval specified of 7.

 

[undemane]

How do I sort the pivot table? My Row Labels are PLANNED_START, Column Labels = FACILITY, and E VALUES = COUNT OF BUILD for the Pivot table.

 

[DonkeyOte]

If the dates are true dates then the values should sort automatically (earliest to latest)

below is a brief outline of what I was talking about - A:B source the Pivot which resides in E:F

Dates have been Grouped by Date with start date set to 11-Oct-2010 and Interval set to 7.
Event is Data Field set to COUNT

Excel Workbook
	A	B	C	D	E	F
1	date	event	11/10/2010		Count of event
2	08/02/2011	a			date	Total
3	07/12/2010	a			11/10/2010 - 17/10/2010	1
4	21/04/2011	a			01/11/2010 - 07/11/2010	1
5	09/04/2011	a			22/11/2010 - 28/11/2010	2
6	14/03/2011	a			06/12/2010 - 12/12/2010	1
7	09/03/2011	a			20/12/2010 - 26/12/2010	1
8	23/04/2011	a			27/12/2010 - 02/01/2011	1
9	30/12/2010	a			24/01/2011 - 30/01/2011	1
10	21/02/2011	a			31/01/2011 - 06/02/2011	1
11	24/11/2010	a			07/02/2011 - 13/02/2011	1
12	17/10/2010	a			14/02/2011 - 20/02/2011	1
13	24/12/2010	a			21/02/2011 - 27/02/2011	1
14	31/03/2011	a			28/02/2011 - 06/03/2011	1
15	01/11/2010	a			07/03/2011 - 13/03/2011	1
16	03/02/2011	a			14/03/2011 - 20/03/2011	1
17	23/11/2010	a			28/03/2011 - 03/04/2011	1
18	25/01/2011	a			04/04/2011 - 10/04/2011	1
19	28/02/2011	a			18/04/2011 - 24/04/2011	2
20	14/02/2011	a			Grand Total	19
Sheet8

 

[undemane]

I am with you on column C, but how did you get the data in col. E-F? I was trying to use a Pivot table, but I don't see where I can do interval = 7. Thank you for your help.

 

[DonkeyOte]

see: http://www.contextures.com/xlpivot07.html

the interface will vary slightly pending version (unknown)

 

[undemane]

Thank you! That worked perfectly!