Need to assign week number to column of dates

[Julieo]

I have a file with 25k+ rows of dates; I need to assign a consecutive week # to a set 7 day date range, (Sat-Sun). I have attached an (short) example of what I am trying to achieve. date example.xls

[oeldere]

I think you can get the information you need, if you click on this link.

http://www.cpearson.com/Excel/WeekNumbers.aspx

[day92]

Does this work for you?

[Julieo]

This seems to be exactly what I need - however I am not so savvy with formulas and am not understanding how to write the formula to produce the results I need.

[Julieo]

Hello day92 - Just saw your post... that is exactly what I need!!! My start / end day has changed to Wed. start / Tues. end - can you tell me which numbers to change in the formula to achieve those results? Thank you!!!!

[Julieo]

Ok, I figured out which numbers to change in the formula. But, I have dates in 2011 & 2012 - I need the week #s to be consecutive into 2012; with the current formula; =INT((AE3-DATE(YEAR(AE3-WEEKDAY(AE3-3)+6),1,6)+WEEKDAY(DATE(YEAR(AE3-WEEKDAY(AE3-4)+6),1,3))+6)/7) the weeks start over with 1 in 2012. Any thoughts??

[daddylonglegs]

So weeks start on Wednesdays.......but when does week 1 start, is there a rule (like 1st Wednesday of the year or 1st Wednesday after a specific date)?

[Julieo]

week one starts with first Wed of the year - but since I need the weeks to run consecutive into 2012; week one would start with first Wed in 2011...

[daddylonglegs]

OK so week 1 starts on Wednesday 5th Jan 2011 and numbering keep going indefinitely without resetting to zero, so today is the first day of week 87?

With date in A2 try this formula for the week number

=INT((A2-DATE(2011,1,5))/7)+1

[Julieo]

Thank you!!! Thank you!!! That works!!!