In some cases, events may not be scheduled for a specific date, but are tied to a specific day of the week of a given month and year – for example:
- the first Monday of January 2007 is the heaviest Monday of the year
- Second Sunday in April 2011 – Air Defense Day
- First Sunday in October 2012 – Teacher’s Day
- etc.
To determine the exact date on which such a day of the week falls, we need a small but tricky formula:
=ДАТА(B1;B2;B4*7-6)+ОСТАТ(B3-ДАТА(B1;B2;);7)
in the English version it will be
=DATE(B1;B2;B4*7-6)+MOD(B3-DATE(B1;B2;);7)
When using this formula, it is assumed that
- B1 – year (number)
- B2 – month number (number)
- B3 – number of the day of the week (Mon=1, Tue=2, etc.)
- B4 – serial number of the day of the week you need
For a significant simplification and improvement of the formula, many thanks to the respected SIT from our Forum.
- How Excel actually stores and processes dates and times
- NeedDate function from PLEX add-on