![]() ![]() Perhaps your requirement is different again? This is easily done of course, using nothing fancier than a little math and the date serial number (covered extensively in both the Essential Skills and Expert skills books). When the 1st of the next month comes around, reset week numbering to “Week 1 again”. “Regard the 1st of each month, and the six days that follow, as “Week 1” and increment the week number every seven days. I’m guessing that your actual requirement is: Your question highlights how important it is to define terminology precisely when designing information systems as “week of the month” is not a carefully enough defined term. There are many ways of defining the “Week of the month” used throughout the world. For this reason 1-4 April 2021 fall in the 1st week of the month and the second week begins on 5th April 2021 so the results you are seeing are correct. ![]() To determine the week number of the current month you are taking the week number of the year and subtracting the week number that the 1st of the month was within. You have also set the week to begin on Monday).ġst Jan 2021 was a Friday so the first three days of 2021 were in Week 1 and then 4th Jan began Week 2.Ģ9 Mar -4 April is thus Week 14 of the year and 5 April to 11 April is Week 15. 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |