Excel 2007 Calculating years to go with week numbers

[chantil]

Hi,

I'm hoping someone might be able to help me. I have a list of records for which I'm trying to calculate year and week number for 2007-2011.

I calculated the ISO week number using the following formula:
=INT((G2-DATE(YEAR(G2-WEEKDAY(G2-1)+4),1,3)+WEEKDAY(DATE(YEAR(G2-WEEKDAY(G2-1)+4),1,3))+5)/7

I had then combined this with the year by using:
=YEAR(G3)&I3 or =YEAR(G3)&"0"&I3 (for wk no. <10) to give the format yyyyww

However the problem arises when part of December falls into Wk1 or January into Wk52/53

E.g. records dated 02/01/2011 are currently being assigned to year 2011 and week 52, when it actually should be year 2010.

It was suggested that I use an if query that stated if the date is before a certain date in January 2011 then if should be 2010. However I'm doing this for a number of years and it's going to have to be repeated so ideally I'd just like one formula that I can cut and paste onto all my spreadsheets containing data for 2007-2011 to reduce the risk of errors.

Trouble is it depends what day of the week the date falls on.

So for example is 28th December 2010 is a Monday then it would be in Wk1 of 2011, however if it’s a Saturday then it’ll be Wk52 2010 as Wk1 would start on the following Monday.

If anyone has any suggestions I'd be really grateful. I'm sure this must be an isssue others have come up against but I've been unable to find any solutions listed anywhere.

Thank you for your time on this matter
Chantil

 

[bluedragon2]

look at the WEEKNUM formula

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[chantil]

Hi,

weeknum doesn't calculate ISO weeks and would still have this issue when trying to add in a year as it would still overlap in weeks around New Year.

Chantil

 

[bluedragon2]

I see your dilemma, was not sure what ISO weeks were until I looked them up. Could have quite a bit of maths associated with it. I will look for a formula, but you could have a lookup table that will assure that you have the correct result.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[GlennUK]

If your week numbers are OK then some simple maths to adjust the year could be done by testing whether
a) the month of the date is high ( like 12 ), and yet the week number is low ( say 1 ), then add 1 to the year
b) the month of the date is low ( like 1 ), and yet the week number is high ( like greater than 50 ), then subtract 1 from the year.

I've got to log off now, otherwise I'd do an example formula for you.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[bluedragon2]

Did find this link for an excel conversion:

http://www.rondebruin.nl/weeknumber.htm

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[chantil]

Hi,

Yes I did. To be honest I think the idea suggested by GlennUK might be the way forward. I've been trying to do something with the date field which would vary each year, whereas a formula based on months would be far simpler and consistent across years.

I'll give that a go.
Thanks!

 

[HasTaLaPasta]

Public Function ISOWeekNum(AnyDate As Date, Optional WhichFormat As Variant) As Integer
' WhichFormat: missing or <> 2 then returns week number,
' = 2 then YYWW
'
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer

ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
Case Is >= NextYearStart
ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
YearNum = Year(AnyDate) + 1
Case Is < ThisYearStart
ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
YearNum = Year(AnyDate) - 1
Case Else
ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
YearNum = Year(AnyDate)
End Select

If IsMissing(WhichFormat) Then Exit Function
If WhichFormat = 2 Then
ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & Format(ISOWeekNum, "00"))
End If
End Function
Public Function YearStart(WhichYear As Integer) As Date

Dim WeekDay As Integer
Dim NewYear As Date

NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0

If WeekDay < 4 Then
YearStart = NewYear - WeekDay
Else
YearStart = NewYear - WeekDay + 7
End If

End Function