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.
=(<span class="str">"1/4/"</span>&YEAR(B2))-MOD(<span class="str">"1/2/"</span>&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 <span class="kwrd">as</span> Long
D = CDate(<span class="str">"1/2/"</span> & year(mydate))
ISOYS = D + 2 - D Mod 7
For more information on ISO dates, see the Power Tips post: Week Numbers in Excel.