Forum Discussion
Calculate 1st Sun, 2nd Sun, 3rd Sun, 4th Sun, and 5th Sun
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.
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+7For the 5th Sunday
=IF( MONTH(C38)=MONTH(C38+7), C38+7, "")
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
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
Do you need this updated for 2020? I noticed your dates are for 2019 which does not have 2/29.
Richard, let me clarify. In C11 where is now "CPE STUDENT"
you'd like to put the date which is first Sunday of the month. You mean next after C11 Sunday, or first Sunday of the month (e.g. December) within which C11 date or something else? Perhaps you may put manually in your sample what it shall be for given C11 (and better to highlight such cells).
I am looking for a formula to place in the highlighted red cells C11, C23, C38, and C50 that will calculate from C1 whether each Sunday is a “1st Sunday”, “2nd Sunday”, “3rd Sunday”, “4th Sunday”, or “5th Sunday” (for that particular month), regardless of the date, placed in C1. Please see attachment.
Thank You.
RichardStill 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.
