View original (French)

Mobile holiday date in Excel

Solved
Philarete Posted messages 39 Status Member -  
Philarete Posted messages 39 Status Member -
Hello,
I am looking for a simple and elegant formula to find, in Excel, the date of the first Sunday following a certain date.

Ideally, the formula should be flexible enough to adapt to various similar operations, for example: finding the date of Epiphany (2nd Sunday after Christmas), finding the date of the 4th Monday after my birthday, etc.

This is to perfect a "perpetual calendar" that is starting to become quite comprehensive...

Thank you in advance!

Configuration: Mac OS X / Safari 533.19.4

2 answers

  2.
    Philarete Posted messages 39 Status Member 1
     
    Thank you, Eriiiic!

    I just found the same answer (on an English site). So, to calculate Epiphany (the 2nd Sunday after Christmas) my formula is:

    =A1+(15-WEEKDAY(A1,1)

    which seems to be correct...

    I will try to understand the thing well so that I can also calculate dates backwards (like the Sunday before a certain date).

    And I will go check the site you mentioned.

    Big and warm thank you. My perpetual calendar is starting to look good.
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Beware of the 2nd parameter of daysem()
      1 or omitted Number between 1 (Sunday) and 7 (Saturday), according to the previous version of Microsoft Excel.
      2 Number between 1 (Monday) and 7 (Sunday).
      3 Number between 0 (Monday) and 6 (Sunday).

      1 corresponds to the English definition of the week (which starts on Sunday there).
      In France, it is better to use 2 or 3, as it simplifies when working on weekends to group the 2 days at the end.

      Eric
      0
    2. Philarete Posted messages 39 Status Member 1
       
      Yes, thank you, I modified the parameter in question (and therefore also the number that serves as the starting point for the addition or subtraction.

      And above all, with that, I well understood how the WEEKDAY function works.

      Thanks again.
      0
    3. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Okay, I'm marking it as resolved for you.
      Eric
      0
    4. Philarete Posted messages 39 Status Member 1
       
      Thank you, Eric. It is indeed resolved.

      In case others come to this thread with the same kind of question, I would like to indicate the formulas I used (with my explanations, because I have a slow and analytical brain):

      Sunday following a certain date ("date"):

      ="date" + (7-WEEKDAY("date", 2)
      (We add to the date the difference between the number of days in the week and the position of the date in question: if the date falls on a Wednesday, day 3 of the week, we end up on the following Sunday by adding 7-3 to the date of the Wednesday).

      Sunday preceding a certain date:

      ="date" - WEEKDAY("date", 2)

      (We find the preceding Sunday by subtracting from the date the number of the day of the week of the date in question: if it's a Wednesday, we go back 3 days to get to the last Sunday).

      Finding the n-th Sunday before a certain date:

      ="date"-(n*7+WEEKDAY("date", 2))

      etc. (don't forget ";2" in the "WEEKDAY" formula, otherwise it counts Sunday as day number 1 instead of Monday).
      0