Weekdays and months

  • Jun. 24, 2019
Example files with this article:
  • Weekdays and months

    • Introduction

    The cleaning lady comes by once every 2 weeks, on Tuesday. Which dates are these for the coming 6 months ? Other example: on the last Monday of every third month, I will donate blood at the local office. Which dates are these ? These are by no means simple questions. Just like the date of the 4th Sunday in June. Like this we can imagine countless scenarios. Wouldn't it be useful to generate this in Excel ? Sure, but when I started the exercise I was puzzled. This isn't easy at all.

    Until I discovered that the WEEKDAY function is a very useful instrument. Of course I knew WEEKDAY but not all of its facets. WEEKDAY as a function has 2 arguments:

    • the date for which you want to ask the weekday
    • the setup of weekdays: what weekday opens the week ?

    Revisiting existing functions

    The result that you want to obtain is also dependent on the setup of the weekdays: we (in Europe) know the system whereby in every week Monday gives 1, Tuesday gives 2, ..., and Sunday gives 7. In other places throughout the world one can start with 1 on Sunday to finish with 7 on Saturday. These are sitations that the WEEKDAY function can handle without any difficulty at all. Moreover, there are other variants:

    • a variant with 0 to 6 instead of 1 to 7
    • other variants with each time a different weekday to start the week: numbers 1 to 7 stand for the Monday to Sunday, or Tuesday to Monday, or Wednesday to Tuesday, or [complete yourself], or Saturday to Sunday.

    All this leads us to conclude that we can easily generate a sequence of 1, 2, 3, ..., 7 for every weekday that could start a week. Put differently, any day can be assigned any of the 7 numbers, depending on the chosen sequence.

    This notion was new to me and I will implement it below to come to for example the last Monday of the month.

    How do you calculate the last Monday of the month ? First you calculate the last day of the month and you assess its weekday. Then you subtract a number of days. If the last day is already a Monday, you won't subtract anything, if not you would subtract a number from 0 to 6:

    If we do the same exercise not only for a Monday but for any chosen day, then we get a table full of values that we need to subtract: iin the rows you will see the weekday of the last day of the month, in the columns you will see the chosen weekday:

    We see the numbers 0 to 6, but in light of the previous, it is useful to add up 1 to each cell:

    Why ? Because of the fact that the sequences 1 to 7 can be found using the WEEKDAY function ! Actually, the WEEKDAY of today, Monday 24 June 2019:

    • 2, if the second argument in the WEEKDAY function is 1 (or empty)
    • 1, if the second argument in the WEEKDAY function is 2
    • 0, if the second argument in the WEEKDAY function is 3
    The value of 1 for Monday, which is the first weekday in the system for Europeans, is pretty much known.

    After that you still have 7 other variants, depending on how the week starts:

    • 1, if the second argument in the WEEKDAY function is 11 (Mon-Sun)
    • 7, if the second argument in the WEEKDAY function is 12 (Tue-Mon)
    • 6, if the second argument in the WEEKDAY function is 13 (Wed-Tue)
    • 5, if the second argument in the WEEKDAY function is 14 (Thu-Wed)
    • 4, if the second argument in the WEEKDAY function is 15 (Fri-Thu)
    • 3, if the second argument in the WEEKDAY function is 16 (Sat-Fri)
    • 2, if the second argument in the WEEKDAY function is 17 (Sun-Sat)

    There you go. We conveniently use the values of 11 to 17 depending on the choices (input variables): please have a detailed look to the formulas in the attached Excel file. 11 to 17 will be retrieved as "10 + the chosen weekday". And it is exactly that number that you apply to the weekday of the last day of the month. No easy explanations here but I think that you can follow the pattern by making use of the tables above.

    The Excel file contains a lot more good stuff in the table:

    • Column A: an incrementing number
    • Column B: the first day of the month (irrespective of what weekday it is)
    • Column C: the last day of the month (irrespective of what weekday it is)
    • Column D: the month number
    • Column E: the year
    • Column F: when the chosen weekday occurs for the first time in the month: "chosen" means cell P3 - Mon/Tue/Wed/Thu/Fri/Sat/Sun
    • Column G: when the chosen weekday occurs for the x-th time in the month
    • Column H: when the chosen weekday occurs for the last time in the month
    • Column I: when the chosen weekday occurs for the first time in the month
    • Column J: when the chosen weekday occurs for the second time in the month
    • Column K: when the chosen weekday occurs for the third time in the month/li>
    • Column L: when the chosen weekday occurs for the fourth time in the month
    • Column M: when the chosen weekday occurs for the fifth time in the month - assuming that this is possible

    For the sake of completeness I want to add that Microsoft Outlook does a pretty good job of scheduling recurring events. Actually, this functionality spurred my interest to mimick the dates in Excel.

    Wishing you lots of fun and keep your heads cool with these tropical temperatures :-)







    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links