# ISOWEEKNum and Year

• Hi Guys,

I am going mad. I am using the following formulae to give the year and ISOWEEKNum

Code
``=YEAR(I3)&" "& TEXT(ISOWEEKNUM(I3),"00")``

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.

• Re: 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;758939

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...

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

if 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:

• It is the first week with a majority (4 or more) of its days in January.
• Its first day is the Monday nearest to 1 January.
• It has 4 January in it. Hence the earliest possible dates are 29 December through 4 January, the latest 4 through 10 January.
• It has the year's first working day in it, if Saturdays, Sundays and 1 January are not working days.

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.

Logic 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!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!