Forum Discussion

Richard_James's avatar
Richard_James
Brass Contributor
Nov 26, 2019
Solved

Calculate 1st Sun, 2nd Sun, 3rd Sun, 4th Sun, and 5th Sun

I am using Microsoft Excell on a Mac computer. I have created the attached 4 Week Microsoft Excell Spreadsheet Calendar Template, which includes calendar dates. I use the calendar repetitively. I am looking for a way to place in cell C1 the date for any particular Sunday, and calculate 1st Sun, 2nd Sun, 3rd Sun, 4th Sun, and 5th Sun of the month, in cells C11, C23, C38, and C50 respectively.

 

Thanks You.

Richard

excel
Excel on Mac
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 26, 2019

    Richard_James 

    Still not sure I understood correctly. If in C3 is 29th of Dec, for this given month December that will 5th Sunday. Formula to calculate

    =(INT(DAY(C3)/7)+1) & CHOOSE(INT(DAY(C3)/7)+1,"st","nd","rd","th","th")& " Sunday"

    In each next block we add 7 to C3. For next block given month will be January, and formula like

    =(INT(DAY(C3+7)/7)+1) & CHOOSE(INT(DAY(C3+7)/7)+1,"st","nd","rd","th","th")& " Sunday"

    etc.

     

     

     

13 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 26, 2019

    Richard_James 

    If in C1 is any date, first Sunday in the month of this date could be calculated as

    =C1-DAY(C1)+8-WEEKDAY(C1-DAY(C1),1)

    If it is in C11, next Sunday in C23 will be

    =C11+7

    For the 5th Sunday

    =IF( MONTH(C38)=MONTH(C38+7), C38+7, "")
    • Richard_James's avatar
      Richard_James
      Brass Contributor
      Nov 26, 2019

      SergeiBaklan 

       

      Thanks for your response.

      However, let me explain more clearly what I am trying the achieve.

      Again, I am using Microsoft Excell on a Mac computer. I have created the attached 4 Week Microsoft Excell Spreadsheet Calendar Template, which includes calendar dates.

      I use the calendar repetitively. I am looking for a way to place in cell C1 the date for any particular Sunday, and calculate 1st Sun, 2nd Sun, 3rd Sun, 4th Sun, and 5th Sun of the month, in cells C11, C23, C38, and C50 respectively.

      I am looking for a formula to place in cells C11, C23, C38, and C50 that will yield either “1st Sunday”, “2nd Sunday”, “3rd Sunday”, “4th Sunday”, or “5th Sunday” (for that particular month), regardless of the date, placed in C1.

       

      Thanks,

      Richard

      • Richard_James's avatar
        Richard_James
        Brass Contributor
        Jan 24, 2020

         

        I am using Microsoft Excell on a Mac computer. I have created the attached 4 Week Microsoft Excell Spreadsheet Calendar Template, which includes calendar dates. I use the calendar repetitively as a template. Please note that this year there are 29 days in February. Thus, beginning in cell U16 forward the date is an error. Can you provide a fix, so that going forward I will not have any errors on the template?

         

         

        Thank You.

        Richard

        Richard_James 

Resources