Appointing a number based on a date (to effectively create a round or week count)

**mrvp** · 10-06-2012, 07:25 AM

I'm looking to add a round or week count to some sports data (so that I can do something else with the round or week number) and I have fixtures for this year's English Premiership based on the date played. You can see that I have managed to figure out the first bit (cell H13, where I have treated the opening day of the season in B2 as effectively the starting point) and was intending to reference everything from here (e.g. after 7 days would be week 2, 14 days from B2 would be week 3 etc.).

I was thinking that I could do a massive IF formula with say 38 terms for 38 weeks, but wondered if there was a slightly more elegant way of doing this.

I'd be grateful for anyone's advice on this and am quite happy to discuss.

Thanks in advance,

Fixtures_Adjustment_12_13.xls

**dwint** · 10-06-2012, 08:31 AM

in cell H2:

=WEEKNUM(DATE(YEAR(B2),MONTH(B2),DAY(B2)-4))-32

copy down

**mrvp** · 10-06-2012, 11:18 AM

Spot on and solved. Another day and a new Excel function to learn about. Looking at it, I figured you'd have to $ the B2 (as I figured that would be the day 1 key), but it actually works around that. Real nice work (I didn't realise you could do sums with dates until this morning, when I came up with the idea to try and do something with some data that I had). I take it the date function is effectively the key to getting the weeknum to refer to B2?, and out of interest the 32 is for?

Thanks again,

**Teethless mama** · 10-06-2012, 12:49 PM

Elegant solution:

=INT((B2-B$2)/7)+1

copy down as far as needed

**mrvp** · 10-06-2012, 04:54 PM

Splitting ends, but more elegant and I get this one, creating an integer based on the B2 and keying it and dividing by 7 (to allow for the week) and the +1, to avoid the 0 issue. Nice.

**daddylonglegs** · 10-08-2012, 11:18 AM

Originally Posted by dwint

=WEEKNUM(DATE(YEAR(B2),MONTH(B2),DAY(B2)-4))-32

This will work OK until you get to 5th January, then you'll get neagtive numbers........

**mrvp** · 10-19-2012, 04:55 PM

Thanks for the heads up on this (and sorry for the lateness of this - I haven't been on the forum in a while). I went with Teethless's solution in the end anyhow as I could figure out the formula for that one, whereas I couldn't figure out dwint's formula. Is it possible doing it that way?

Appointing a number based on a date (to effectively create a round or week count)

LinkBack

Thread Tools

Rate This Thread

Display

Appointing a number based on a date (to effectively create a round or week count)

Re: Appointing a number based on a date (to effectively create a round or week count)

Re: Appointing a number based on a date (to effectively create a round or week count)

Re: Appointing a number based on a date (to effectively create a round or week count)

Re: Appointing a number based on a date (to effectively create a round or week count)

Re: Appointing a number based on a date (to effectively create a round or week count)

Re: Appointing a number based on a date (to effectively create a round or week count)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions