In the case of 1st April 2021 this is week 14 of the year (you have opted for System 1 when the week containing January 1st is considered the first week of the year and is numbered 1. The named range CalendarYear in the example above is the year under consideration.įor Solution 3 you’d have to study Session 4 in the above book but it enables the same result with a very simple function call by moving much of the logic to a separate table (without any use of macros or VBA code).ġ/ The week number that the first day of the month falls within. The book explains in simple terms how each method can be implemented and how the functions work but, since you asked, here are the formulas (in the context of Martin Luthor King day which falls on the third Monday in January). The first two methods are quite complex but the last makes this type of calculation very simple. A novel generic solution that uses a simple four argument function call to return the correct answer. By combining the DATE, CHOOSE and WEEKDAY functions.ģ. By combining the DATE, WEEKDAY and MOD functions.Ģ. Session 4 of the book suggests three different ways to solve this problem.ġ. The problem arises because months have a variable number of days and can begin on a different day each year. For example, Martin Luther King day is the third Monday in January.įinding dates such as the second Thursday in January is very simple for humans to understand but is a little more challenging for Excel.

Naruto volume 1 book buy