# Calculate the ISO Start of Year date

Today’s author is Shane Devenshire, who has been an Excel MVP for many years and who regularly contributes to Excel Newsgroups. He has published over 300 articles in different computer magazines, and co-authored numerous books. Excel might be better today because of the many bugs he posted when he was a Beta tester! This post shows you how to use a formula or custom VBA function to calculate the ISO Start of Year date.

Here is a formula for calculating the ISO Start of Year date for any legal Excel date.

`=("1/4/"&YEAR(B2))-MOD("1/2/"&YEAR(B2),7)`

This assumes the date you are entering is in B2. With this formula you enter any date in a year and Excel returns the ISO Start of Year date. For example if you enter 9/12/09 in B2 the result of this formula is 12/29/08.

You can create a custom user-defined function (UDF) using VBA to perform the same calculation. To do that, open the Visual Basic editor, click Module on the Insert menu, and then copy the following function into the module. After adding this UDF to your workbook, you can use it like a built-in function =ISOYS(B4):

```'Returns Start of Year date (1st Monday) based on an input date
Public Function ISOYS(mydate As Date) As Date
Dim D as Long

D = CDate("1/2/" & year(mydate))
ISOYS = D + 2 - D Mod 7
End Function```

For more information on ISO dates, see the Power Tips post: Week Numbers in Excel.

• for the Europeans who use dd/mm/yyyy format, the formula needs to be adapted to that specific format..

=("4/1/"&YEAR(E12))-MOD("2/1/"&YEAR(E12);7)

or we could try this one

=("04 jan "&YEAR(E12))-MOD("02 jan "&YEAR(E12);7)

which should work for most of us ?!

• Looking at my post I realize

1. That I substituted B2 with E12 (I pasted this in a workbook that had already other data in cell B2....)

2. That I substituted comma's with semicolon's to get this to work in my European version of Excel

just so that you don't loose time figuring out why this doesn't work on your computer

• I think we should avoid formulas that depend on national date format conventions - especially when computing an ISO value. I like to use functions that conform to the only internation date format yyyy/mm/dd.

I prefer

=(DATE(YEAR(B2),1,4)-MOD(DATE(YEAR(B2),1,2),7))

In the UDF, change the assingment of D to

D = DateSerial(Year(mydate), 1, 2)

• Another exquisite example of the utter stupidity of using this blog to try to duplicate what newsgroups do so much better!

First off, the perfect turn of phrase: 'Excel might be better today because of the many bugs he posted when he was a Beta tester!' Perhaps Excel MIGHT if those bugs had been fixed. Finally an example of where an English major on staff would have been useful.

Anyway, start off with the error of using locale-specific date formats and compound by using MOD, which depends on using the 1900 date system. That is, the correct start of the ISO year 2009 is 29 Dec 2008, but Shane's and Bernard's formulas both return 4 Jan 2009 under the 1904 date system.

The best solution is still found at

www.cpearson.com/.../weeknum.htm

Part of wisdom is knowing which wheels don't need to be reinvented.

• cpearson's approach produces week 0 for 1-Jan-2010. I'm not an expert on ISO dates, but that doesn't look right.

• Chip's approach does fail for 2010. So add it to the list of nonrobust solutions.

BTW, Shane's and Bernard's formulas also fail for 1Jan2010 thru 3Jan2010, so no one else seems to have bothered to test their wonderful formulas.

Fine bit of QA by the Microsoft people who posted this.

I've played with this now (1st time since I have no use myself for ISO week numbers), and the shortest formula for the last day of the previous ISO year I've come up with is

=INDEX(DATE(YEAR(A1+{0;-3;3}),1,IF(WEEKDAY(DATE(YEAR(A1+{0;-3;3}),1,1),2)YEAR(A1-3),WEEKDAY(A1,2)>3+DAY(A1))+2*AND(YEAR(A1)

• This is working with the date in B4 Rob

=INT((B8-DATE(YEAR(B8-WEEKDAY(B8-1)+4),1,3)+WEEKDAY(DATE(YEAR(B8-WEEKDAY(B8-1)+4),1,3))+5)/7)