Hi Guys,
I am going mad. I am using the following formulae to give the year and ISOWEEKNum
This works great with the exception of end of year when the year is wrong ie if I used the date as 30/12/2013 it would give me 2013 01 rather than 2014 01. Is there a formulae I can use for certain dates to return the correct year, or will I have to create a custom function to account for these?
Any help appreciated
Re: ISOWEEKNum and Year
Is anyone able to help?
I should have said that I'm using excel 2013, but not sure its possible to correct the year?
Re: ISOWEEKNum and Year
ISO Week numbers follow certain rules:
A week starts Monday - Always
The first week in a year must contain at least 4 days in that year, so a week with Mon, Tues or Wed in the earlier year and the Thursday in the next calendar year is ISO Week 1 of the following calendar year .
Leading from that, you cannot assume the ISO year of a date in the last 4 days of a year.
Courtesy of RdB (Who credits someone else in the snippet...), a UDF to calculate the calendar date of an ISO year start.
Public Function ISOYEARSTART(WhichYear As Integer) As Date
' First published by John Green, Excel MVP, Sydney, Australia
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7
If WeekDay < 4 Then
ISOYEARSTART = NewYear - WeekDay
Else
ISOYEARSTART = NewYear - WeekDay + 7
End If
End FunctionRe: ISOWEEKNum and Year
Thanks, funnily enough my vba code was similar, but was wondering if there was a formula that I was missing(nesting if was my thought). But confirming it couldn't be done keeps my mind at rest, so thank you!
Re: ISOWEEKNum and Year
Confused... What can't be done?
Your formula takes the year of a date (No problem) then the ISO week number of that date (1, as the date is actually ISO week 1 of the next calendar year) but then concatenates those 2 returns, giving the wrong result...
You need to check the ISO year start date (UDF above) against the date in I3. If less than that date, then you need to add 1 to the year...
Re: ISOWEEKNum and Year
Quote from cytop;758939Confused... What can't be done?
Your formula takes the year of a date (No problem) then the ISO week number of that date (1, as the date is actually ISO week 1 of the next calendar year) but then concatenates those 2 returns, giving the wrong result...
You need to check the ISO year start date (UDF above) against the date in I3. If less than that date, then you need to add 1 to the year...
Hello,
Sorry I was merely trying to check if this was possible to do without a udf that is all. But as its not will just use code to get round it. Thank you for your advice
Re: ISOWEEKNum and Year
That UDF can be (probably) be converted to a cell formula - it'd be a PITA to type, but could be done... the UDF is simpler if you can use it.
Re: ISOWEEKNum and Year
Ok, it seems a udf is now fine! However, I am still getting errors
Public Function ISOYEARNUM(WhichYear As Integer) As Date
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7
If WeekDay < 4 Then
ISOYEARNUM = Format(NewYear - WeekDay, "YYYY")
Else
ISOYEARNUM = Format(NewYear - WeekDay + 7, "YYYY")
End If
End Functionif I have a date in cell AB180 of 18/12/2015 and my formulae in cell AC180 is =ISOYEARNUM(YEAR(AB180))&" "& TEXT(ISOWEEKNUM(AB180),"00"), it gives me "2014 51", when it should be "2015 51". Have I done something wrong?
Re: ISOWEEKNum and Year
Thinking on, I think the UDF has to be a little more involved
The ISO 8601 definition for week 01 is the week with the year's first Thursday in it. The following definitions based on properties of this week are mutually equivalent, since the ISO week starts with Monday:
If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, it is part of week 53 of the previous year; if on a Saturday, it is part of week 52 (or 53 if the previous year was a leap year); if on a Sunday, it is part of week 52 of the previous year.
Re: ISOWEEKNum and Year
Im close to throwing the pc now!! Anyone able to stop me having a breakdown?!
Re: ISOWEEKNum and Year
Solved as follows. The reason why I was having a break down was because I was focusing on the day.... Actually I need to focus on which month the date is in and which ISOWEEKNUM.
Public Function ISOYEARNUM(dte As Date)
ISOYEARNUM = Year(dte)
If WorksheetFunction.ISOWeekNum(dte) = 1 Then
If Month(dte) <> 1 Then
ISOYEARNUM = Year(dte) + 1
End If
End If
If WorksheetFunction.ISOWeekNum(dte) >= 52 Then
If Month(dte) = 1 Then
ISOYEARNUM = Year(dte) - 1
End If
End If
End FunctionLogic being that if week 1 isn't in month 1, an adjustment needs to be made as does if week 52 or 53 but not month 12. Calling formulae in excel 2013 as such yields the answer I was looking for =ISOYEARNUM(Q132)&" "& TEXT(ISOWEEKNUM(Q132),"00"). Tested for next 50 years and all good!
Don’t have an account yet? Register yourself now and be a part of our community!