I am working on a project that requires to me find out the date of the week
for all the calender days in a year. For example, 3/2/09 - 3/8/09 falls in
the week of 3/2/09 assuming that my week starts on Monday and ends on Sunday.

I've been browsing through all the formulas and have tried many combinations
of the IF and DATE functions, but without success. Appreciate any input!

My table will look like this --- I need to compute the "week of" date,
without having to enter this information manually.


Date Week of
11/17/2008 11/17/2008
11/18/2008 11/17/2008
11/19/2008 11/17/2008
11/20/2008 11/17/2008
11/21/2008 11/17/2008
11/22/2008 11/17/2008
11/23/2008 11/17/2008
11/24/2008 11/24/2008
11/25/2008 11/24/2008
11/26/2008 11/24/2008
11/27/2008 11/24/2008
11/28/2008 11/24/2008
11/29/2008 11/24/2008
11/30/2008 11/24/2008

Hi,

assuming your first date in A2 put this in b2 and drag down

=IF(WEEKDAY(A2)=1,A2-6,A2-(WEEKDAY(A2)-2))

Mike

"ExcelUser09" wrote:

I am working on a project that requires to me find out the date of the week
for all the calender days in a year. For example, 3/2/09 - 3/8/09 falls in
the week of 3/2/09 assuming that my week starts on Monday and ends on Sunday.

I've been browsing through all the formulas and have tried many combinations
of the IF and DATE functions, but without success. Appreciate any input!

My table will look like this --- I need to compute the "week of" date,
without having to enter this information manually.


Date Week of
11/17/2008 11/17/2008
11/18/2008 11/17/2008
11/19/2008 11/17/2008
11/20/2008 11/17/2008
11/21/2008 11/17/2008
11/22/2008 11/17/2008
11/23/2008 11/17/2008
11/24/2008 11/24/2008
11/25/2008 11/24/2008
11/26/2008 11/24/2008
11/27/2008 11/24/2008
11/28/2008 11/24/2008
11/29/2008 11/24/2008
11/30/2008 11/24/2008

Try this:

=1-WEEKDAY(A2,2)+A2

Copy down as needed.

--
Biff
Microsoft Excel MVP


"ExcelUser09" wrote in message
...
I am working on a project that requires to me find out the date of the week
for all the calender days in a year. For example, 3/2/09 - 3/8/09 falls in
the week of 3/2/09 assuming that my week starts on Monday and ends on
Sunday.

I've been browsing through all the formulas and have tried many
combinations
of the IF and DATE functions, but without success. Appreciate any input!

My table will look like this --- I need to compute the "week of" date,
without having to enter this information manually.


Date Week of
11/17/2008 11/17/2008
11/18/2008 11/17/2008
11/19/2008 11/17/2008
11/20/2008 11/17/2008
11/21/2008 11/17/2008
11/22/2008 11/17/2008
11/23/2008 11/17/2008
11/24/2008 11/24/2008
11/25/2008 11/24/2008
11/26/2008 11/24/2008
11/27/2008 11/24/2008
11/28/2008 11/24/2008
11/29/2008 11/24/2008
11/30/2008 11/24/2008

hi
see this site.
http://www.rondebruin.nl/weeknumber.htm

may get you started.
Regards
FSt1

"ExcelUser09" wrote:

I am working on a project that requires to me find out the date of the week
for all the calender days in a year. For example, 3/2/09 - 3/8/09 falls in
the week of 3/2/09 assuming that my week starts on Monday and ends on Sunday.

I've been browsing through all the formulas and have tried many combinations
of the IF and DATE functions, but without success. Appreciate any input!

My table will look like this --- I need to compute the "week of" date,
without having to enter this information manually.


Date Week of
11/17/2008 11/17/2008
11/18/2008 11/17/2008
11/19/2008 11/17/2008
11/20/2008 11/17/2008
11/21/2008 11/17/2008
11/22/2008 11/17/2008
11/23/2008 11/17/2008
11/24/2008 11/24/2008
11/25/2008 11/24/2008
11/26/2008 11/24/2008
11/27/2008 11/24/2008
11/28/2008 11/24/2008
11/29/2008 11/24/2008
11/30/2008 11/24/2008

Check your other post, too.

ExcelUser09 wrote:

I am working on a project that requires to me find out the date of the week
for all the calender days in a year. For example, 3/2/09 - 3/8/09 falls in
the week of 3/2/09 assuming that my week starts on Monday and ends on Sunday.

I've been browsing through all the formulas and have tried many combinations
of the IF and DATE functions, but without success. Appreciate any input!

My table will look like this --- I need to compute the "week of" date,
without having to enter this information manually.

Date Week of
11/17/2008 11/17/2008
11/18/2008 11/17/2008
11/19/2008 11/17/2008
11/20/2008 11/17/2008
11/21/2008 11/17/2008
11/22/2008 11/17/2008
11/23/2008 11/17/2008
11/24/2008 11/24/2008
11/25/2008 11/24/2008
11/26/2008 11/24/2008
11/27/2008 11/24/2008
11/28/2008 11/24/2008
11/29/2008 11/24/2008
11/30/2008 11/24/2008

--

Dave Peterson