formula to increment week number in excel

[tizwaz]

Can anyone help. I want to have a spreadsheet to have a date for every weekday and then for each of those days have a week and day number based on a 4 week rota. Is there anyway of doing this by a formula? for eg the 1st week would be week 1 day 1 to week 1 day 5 and then the subsequent weeks would be week 2 (days 1-5), week 3 (days 1-5), week4 (days 1-5)and then it starts again with the next week being week 1 (days 1-5). As I hope to have the spreadsheet covering several years I don't really want to have to copy and paste or do it manually. To complicate matters further each week will start on the Tuesday and end on the following Monday (weekends not included)

 

[SkipVought]

hi,

I use this formula to calculate a week number. See if it can be used.
[tt]
=INT((YourDateRef-3)/7)*7+3
[/tt]
Vary the -3+3 number to shift the pivot date. I usually use Monday, which is -2+2. -3+3 pivots on Tuesday.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Here's what that would look like, where the formula is in column 3...
[tt]
Date Day WeekOf
6/15/2012 Fri 6/12/2012
6/16/2012 Sat 6/12/2012
6/17/2012 Sun 6/12/2012
6/18/2012 Mon 6/12/2012
6/19/2012 Tue 6/19/2012
6/20/2012 Wed 6/19/2012
6/21/2012 Thu 6/19/2012
6/22/2012 Fri 6/19/2012
6/23/2012 Sat 6/19/2012
6/24/2012 Sun 6/19/2012
6/25/2012 Mon 6/19/2012
6/26/2012 Tue 6/26/2012
6/27/2012 Wed 6/26/2012
6/28/2012 Thu 6/26/2012
6/29/2012 Fri 6/26/2012
6/30/2012 Sat 6/26/2012
7/1/2012 Sun 6/26/2012
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tizwaz]

thanks Skip but I think I may have not explained v well

I need it to do something like this

Tuesday 12/06/12 week 1 day 1
wednesday 13/6/12 week 1 day 2
thursday 14/6/12 week 1 day 3

incrementing so i have the days listed as week 1 day 1 - 5 followed by week 2 day 1 - 5 and so on on a 4 week loop up to week 4 day 5 and then starting again at week 1 day 1

 

[SkipVought]

This part of the formula will do that for you
[tt]
=INT((YourDateRef-3)/7)
[/tt]
just offset it (subtract a fixed value) to adjust it to 1, for whatever beginning date works

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Ohhhhhh.

You want the week of the MONTH. I see a 4 week rotation. No 5th week ever, which wouldn't be week of the month then.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Set up a day of week table
[tt]
DOW
Tue
Wed
Thu
Fri
Mon
[/tt]

WEEK NUM...
[tt]
=MOD(INT((YourDateRef-3)/7)-5892,4)+1
[/tt]
DAY NUM, using a Named Range for the day of week table...
[tt]
=MATCH(TEXT(YourDateRef,"ddd"),DOW,0)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tizwaz]

thanks that's great and giving me week numbers and day numbers. One thing I don't understand where the week number comes from - how to I tell it where to start numbering from - there is no set numbering that for eg it starts the year with week 1 it is just a rolling 4 week period and I would need to tell it what date to start numbering from - how would i do that

 

[SkipVought]

this returns a number that is calculated from your date.
[tt]
=INT((YourDateRef-3)/7)
[/tt]
You can use the number that this returns for a particular date to SUBTRACT as I did here...
[tt]
=MOD(INT((YourDateRef-3)/7)-5892,4)+1
[/tt]
the MOD(x,4) function will return 0,1,2,3, which is why I add 1 to make the entire expression return 1,2,3,4.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]