Excel to recognize a series of dates as a particular week in the year

**Kendra** · 11-12-2008, 12:41 PM

Hey there everyone!

I am new to this site and am searching for tips on how to create a certian type of formula.

My intentions are for Excel to recognize a series of dates as a particular week in the year. For example: 12/28/2008 thru 1/3/2009 equals Week 1, 1/4/2009 thru 1/10/09 equals Week 2.

I cannot for the life of me calculate a working formula.

Seeking Help,
Kendra

**JBeaucaire** · 11-12-2008, 12:56 PM

Simplest way is to put a chart somewhere in your workbook that will use a simple VLOOKUP against the date to choose the proper week. (See attached)

Once it's created, you can hide those columns if desired.

**modytrane** · 11-12-2008, 01:52 PM

OR try the formula approach in the attached example.

Enter a date in column H [up to row 42] and the week numer will show in column J. You can extend the formula down in column J.

modytrane

**modytrane** · 11-12-2008, 01:57 PM

I forgot to mention that Cell C5 has start date for the year.
You can change it to start another year [for future].
modytrane.

**Kendra** · 11-12-2008, 02:14 PM

To Modytrane,

I like the spreadsheet you created but am still unsure how to modify it. The week numbers are off by one week. If you look at a calander, 5/31/2009 - 6/6/2009 fall under week 22 of the year and 6/7/2009 - 6/13/2009 fall under week 23... and so on. How can I adjust it so the weeks are correct?

**arthurbr** · 11-12-2008, 02:19 PM

It all depends in which manner weeks are numbered !
Have a look at this explanation

**modytrane** · 11-12-2008, 02:35 PM

I have adjusted the formula for you.
Cell C5 has start date for week no. 1.
So first seven days starting from that day will be week no.1.
In case of the attached example that would be 12/27/08 to 1/3/09.

1/4/09 would then be start of week no.2. and so on.
Every seven days after that week number will advance by 1.
Use Cell C5 to start another year.

Hope it helps.
modytrane