How to Create a Custom Calendar in Google Sheets Pt.1

Summary

In this tutorial, we show you step by step how our Custom Forever Calendar was designed. We call it our Forever Calendar since it can be used over and over again without needing to reformat dates and columns. 

This tutorial is broken down into 5 Parts and this article contains Parts 1 and 2. 

  • Part 1 - Creating the Calendar Set Up and Titles.
  • Part 2 - Creating the Annual Calendar.
  • Part 3 - Creating the Holiday and Personal Dates List.
  • Part 4 - Creating the Monthly Calendars.
  • Part 5 - Creating the Conditional Formatting on the Annual Calendar.

Here are all the rows and columns you'll need for the Annual Calendar.
  • Rows 1 to 55. 
  • Columns A to AE. 
Here are all the rows and columns you'll need for the Monthly Calendars.
  • Rows 1 to 34. 
  • Columns A to R.

 Part 1 - Creating the Calendar Set Up & Titles.

Figure 1. Calendar Set Up


Before we began, title this tab "Year".  To create the set up, please do the following.
  1. Open a new Google Sheets.
  2. For Cells B2 to X2, change the column size to 22 and row size to 25. Merge Cells B2 to X2 and type "Calendar Set Up".
  3. Merge Cells B4 to E4 and type "Year".
  4. Merge Cells B6 to E6 and type "Month".
  5. Merge Cells I4 to O4 and type "Start Day".
  6. Merge Cells I6 to O6 and type "Calendar Title".
  7. Merge Cells F6 to H6 and create dropdown with values 1 to 12 for each month. 
  8. Merge Cells P4 to R4 and create a dropdown with values 1 to 7 for each weekday.
  9. Merge Cells B9 to X9 and type this formula, "=IF($F$6=1,F4,F4&"-"&F4+1)". 
    Figure 2. Calendar Year
  10. Merge Cells Z9 to AD9 and type this formula, "=IF(P6="","",P6)". 
    Figure 3. Calendar Title

Part 2 - Creating the Annual Calendar. 

Figure 4. Annual Calendar with Conditional Formatting.


This part is broken down by month. We'll start with formulas need to create January  and then work our way down to December. 

January Formulas. 
  1. Merge B11 to H12 and type "=DATE(Year!$F$4,Year!$F$6,1)".
  2. In Cell B13, type "=INDEX({"Su";"M";"Tu";"W";"Th";"F";"Sa"},1+MOD(Year!$P$4+1-2,7))".
  3. In Cell C13, type "=INDEX({"Su";"M";"Tu";"W";"Th";"F";"Sa"},1+MOD(Year!$P$4+2-2,7))".
  4. In Cell D13, type "=INDEX({"Su";"M";"Tu";"W";"Th";"F";"Sa"},1+MOD(Year!$P$4+3-2,7))".
  5. In Cell E13, type "=INDEX({"Su";"M";"Tu";"W";"Th";"F";"Sa"},1+MOD(Year!$P$4+4-2,7))".
  6. In Cell F13, type "=INDEX({"Su";"M";"Tu";"W";"Th";"F";"Sa"},1+MOD(Year!$P$4+5-2,7))".
  7. In Cell G13, type "=INDEX({"Su";"M";"Tu";"W";"Th";"F";"Sa"},1+MOD(Year!$P$4+6-2,7))".
  8. In Cell H13, type "=INDEX({"Su";"M";"Tu";"W";"Th";"F";"Sa"},1+MOD(Year!$P$4+7-2,7))".
  9. In Cell B14, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B14)-ROW(Year!$B$14))*7+(COLUMN(Year!B14)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B14)-ROW(Year!$B$14))*7+(COLUMN(Year!B14)-COLUMN(Year!$B$14)+1))".
  10. In Cell C14, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C14)-ROW(Year!$B$14))*7+(COLUMN(Year!C14)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C14)-ROW(Year!$B$14))*7+(COLUMN(Year!C14)-COLUMN(Year!$B$14)+1))".
  11. In Cell D14, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D14)-ROW(Year!$B$14))*7+(COLUMN(Year!D14)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D14)-ROW(Year!$B$14))*7+(COLUMN(Year!D14)-COLUMN(Year!$B$14)+1))".
  12. In Cell E14, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E14)-ROW(Year!$B$14))*7+(COLUMN(Year!E14)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E14)-ROW(Year!$B$14))*7+(COLUMN(Year!E14)-COLUMN(Year!$B$14)+1))".
  13. In Cell F14, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F14)-ROW(Year!$B$14))*7+(COLUMN(Year!F14)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F14)-ROW(Year!$B$14))*7+(COLUMN(Year!F14)-COLUMN(Year!$B$14)+1))".
  14. In Cell G14, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G14)-ROW(Year!$B$14))*7+(COLUMN(Year!G14)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G14)-ROW(Year!$B$14))*7+(COLUMN(Year!G14)-COLUMN(Year!$B$14)+1))".
  15. In Cell H14, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H14)-ROW(Year!$B$14))*7+(COLUMN(Year!H14)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H14)-ROW(Year!$B$14))*7+(COLUMN(Year!H14)-COLUMN(Year!$B$14)+1))".
  16. In Cell B15, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B15)-ROW(Year!$B$14))*7+(COLUMN(Year!B15)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B15)-ROW(Year!$B$14))*7+(COLUMN(Year!B15)-COLUMN(Year!$B$14)+1))".
  17. In Cell C15, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C15)-ROW(Year!$B$14))*7+(COLUMN(Year!C15)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C15)-ROW(Year!$B$14))*7+(COLUMN(Year!C15)-COLUMN(Year!$B$14)+1))".
  18. In Cell D15, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D15)-ROW(Year!$B$14))*7+(COLUMN(Year!D15)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D15)-ROW(Year!$B$14))*7+(COLUMN(Year!D15)-COLUMN(Year!$B$14)+1))".
  19. In Cell E15, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E15)-ROW(Year!$B$14))*7+(COLUMN(Year!E15)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E15)-ROW(Year!$B$14))*7+(COLUMN(Year!E15)-COLUMN(Year!$B$14)+1))".
  20. In Cell F15, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F15)-ROW(Year!$B$14))*7+(COLUMN(Year!F15)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F15)-ROW(Year!$B$14))*7+(COLUMN(Year!F15)-COLUMN(Year!$B$14)+1))".
  21. In Cell G15, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G15)-ROW(Year!$B$14))*7+(COLUMN(Year!G15)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G15)-ROW(Year!$B$14))*7+(COLUMN(Year!G15)-COLUMN(Year!$B$14)+1))".
  22. In Cell H15, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H15)-ROW(Year!$B$14))*7+(COLUMN(Year!H15)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H15)-ROW(Year!$B$14))*7+(COLUMN(Year!H15)-COLUMN(Year!$B$14)+1))".
  23. In Cell B16, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B16)-ROW(Year!$B$14))*7+(COLUMN(Year!B16)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B16)-ROW(Year!$B$14))*7+(COLUMN(Year!B16)-COLUMN(Year!$B$14)+1))".
  24. In Cell C16, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C16)-ROW(Year!$B$14))*7+(COLUMN(Year!C16)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C16)-ROW(Year!$B$14))*7+(COLUMN(Year!C16)-COLUMN(Year!$B$14)+1))".
  25. In Cell D16, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D16)-ROW(Year!$B$14))*7+(COLUMN(Year!D16)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D16)-ROW(Year!$B$14))*7+(COLUMN(Year!D16)-COLUMN(Year!$B$14)+1))".
  26. In Cell E16, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E16)-ROW(Year!$B$14))*7+(COLUMN(Year!E16)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E16)-ROW(Year!$B$14))*7+(COLUMN(Year!E16)-COLUMN(Year!$B$14)+1))".
  27. In Cell F16, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F16)-ROW(Year!$B$14))*7+(COLUMN(Year!F16)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F16)-ROW(Year!$B$14))*7+(COLUMN(Year!F16)-COLUMN(Year!$B$14)+1))".
  28. In Cell G16, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G16)-ROW(Year!$B$14))*7+(COLUMN(Year!G16)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G16)-ROW(Year!$B$14))*7+(COLUMN(Year!G16)-COLUMN(Year!$B$14)+1))".
  29. In Cell H16, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H16)-ROW(Year!$B$14))*7+(COLUMN(Year!H16)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H16)-ROW(Year!$B$14))*7+(COLUMN(Year!H16)-COLUMN(Year!$B$14)+1))".
  30. In Cell B17, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B17)-ROW(Year!$B$14))*7+(COLUMN(Year!B17)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B17)-ROW(Year!$B$14))*7+(COLUMN(Year!B17)-COLUMN(Year!$B$14)+1))".
  31. In Cell C17, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C17)-ROW(Year!$B$14))*7+(COLUMN(Year!C17)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C17)-ROW(Year!$B$14))*7+(COLUMN(Year!C17)-COLUMN(Year!$B$14)+1))".
  32. In Cell D17, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D17)-ROW(Year!$B$14))*7+(COLUMN(Year!D17)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D17)-ROW(Year!$B$14))*7+(COLUMN(Year!D17)-COLUMN(Year!$B$14)+1))".
  33. In Cell E17, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E17)-ROW(Year!$B$14))*7+(COLUMN(Year!E17)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E17)-ROW(Year!$B$14))*7+(COLUMN(Year!E17)-COLUMN(Year!$B$14)+1))".
  34. In Cell F17, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F17)-ROW(Year!$B$14))*7+(COLUMN(Year!F17)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F17)-ROW(Year!$B$14))*7+(COLUMN(Year!F17)-COLUMN(Year!$B$14)+1))".
  35. In Cell G17, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G17)-ROW(Year!$B$14))*7+(COLUMN(Year!G17)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G17)-ROW(Year!$B$14))*7+(COLUMN(Year!G17)-COLUMN(Year!$B$14)+1))".
  36. In Cell H17, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H17)-ROW(Year!$B$14))*7+(COLUMN(Year!H17)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H17)-ROW(Year!$B$14))*7+(COLUMN(Year!H17)-COLUMN(Year!$B$14)+1))".
  37. In Cell B18, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B18)-ROW(Year!$B$14))*7+(COLUMN(Year!B18)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B18)-ROW(Year!$B$14))*7+(COLUMN(Year!B18)-COLUMN(Year!$B$14)+1))".
  38. In Cell C18, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C18)-ROW(Year!$B$14))*7+(COLUMN(Year!C18)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C18)-ROW(Year!$B$14))*7+(COLUMN(Year!C18)-COLUMN(Year!$B$14)+1))".
  39. In Cell D18, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D18)-ROW(Year!$B$14))*7+(COLUMN(Year!D18)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D18)-ROW(Year!$B$14))*7+(COLUMN(Year!D18)-COLUMN(Year!$B$14)+1))".
  40. In Cell E18, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E18)-ROW(Year!$B$14))*7+(COLUMN(Year!E18)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E18)-ROW(Year!$B$14))*7+(COLUMN(Year!E18)-COLUMN(Year!$B$14)+1))".
  41. In Cell F18, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F18)-ROW(Year!$B$14))*7+(COLUMN(Year!F18)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F18)-ROW(Year!$B$14))*7+(COLUMN(Year!F18)-COLUMN(Year!$B$14)+1))".
  42. In Cell G18, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G18)-ROW(Year!$B$14))*7+(COLUMN(Year!G18)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G18)-ROW(Year!$B$14))*7+(COLUMN(Year!G18)-COLUMN(Year!$B$14)+1))".
  43. In Cell H18, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H18)-ROW(Year!$B$14))*7+(COLUMN(Year!H18)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H18)-ROW(Year!$B$14))*7+(COLUMN(Year!H18)-COLUMN(Year!$B$14)+1))".
  44. In Cell B19, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B19)-ROW(Year!$B$14))*7+(COLUMN(Year!B19)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!B19)-ROW(Year!$B$14))*7+(COLUMN(Year!B19)-COLUMN(Year!$B$14)+1))".
  45. In Cell C19, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C19)-ROW(Year!$B$14))*7+(COLUMN(Year!C19)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!C19)-ROW(Year!$B$14))*7+(COLUMN(Year!C19)-COLUMN(Year!$B$14)+1))".
  46. In Cell D19, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D19)-ROW(Year!$B$14))*7+(COLUMN(Year!D19)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!D19)-ROW(Year!$B$14))*7+(COLUMN(Year!D19)-COLUMN(Year!$B$14)+1))".
  47. In Cell E19, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E19)-ROW(Year!$B$14))*7+(COLUMN(Year!E19)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!E19)-ROW(Year!$B$14))*7+(COLUMN(Year!E19)-COLUMN(Year!$B$14)+1))".
  48. In Cell F19, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F19)-ROW(Year!$B$14))*7+(COLUMN(Year!F19)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!F19)-ROW(Year!$B$14))*7+(COLUMN(Year!F19)-COLUMN(Year!$B$14)+1))".
  49. In Cell G19, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G19)-ROW(Year!$B$14))*7+(COLUMN(Year!G19)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!G19)-ROW(Year!$B$14))*7+(COLUMN(Year!G19)-COLUMN(Year!$B$14)+1))".
  50. In Cell H19, type "=IF(MONTH(Year!$B$11)<>MONTH(Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H19)-ROW(Year!$B$14))*7+(COLUMN(Year!H19)-COLUMN(Year!$B$14)+1)),"",Year!$B$11-(WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$B$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!H19)-ROW(Year!$B$14))*7+(COLUMN(Year!H19)-COLUMN(Year!$B$14)+1))".

February  Formulas. 
  1. Merge J11 to P12 and type "=DATE(YEAR(Year!B11),MONTH(Year!B11)+1,1)".
  2. In Cell J13, type "=Year!$B$13".
  3. In Cell K13, type "=Year!$C$13".
  4. In Cell L13, type "=Year!$D$13".
  5. In Cell M13, type "=Year!$E$13".
  6. In Cell N13, type "=Year!$F$13".
  7. In Cell O13, type "=Year!$G$13".
  8. In Cell P13, type "=Year!$H$13".
  9. In Cell J14, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!J14)-ROW(Year!$J$14))*7+(COLUMN(Year!J14)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(Year!J14)-ROW(Year!$J$14))*7+(COLUMN(Year!J14)-COLUMN(Year!$J$14)+1))".
  10. In Cell K14, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K14)-ROW(Year!$J$14))*7+(COLUMN(K14)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K14)-ROW(Year!$J$14))*7+(COLUMN(K14)-COLUMN(Year!$J$14)+1))".
  11. In Cell L14, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L14)-ROW(Year!$J$14))*7+(COLUMN(L14)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L14)-ROW(Year!$J$14))*7+(COLUMN(L14)-COLUMN(Year!$J$14)+1))".
  12. In Cell M14, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M14)-ROW(Year!$J$14))*7+(COLUMN(M14)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M14)-ROW(Year!$J$14))*7+(COLUMN(M14)-COLUMN(Year!$J$14)+1))".
  13. In Cell N14, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N14)-ROW(Year!$J$14))*7+(COLUMN(N14)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N14)-ROW(Year!$J$14))*7+(COLUMN(N14)-COLUMN(Year!$J$14)+1))".
  14. In Cell O14, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O14)-ROW(Year!$J$14))*7+(COLUMN(O14)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O14)-ROW(Year!$J$14))*7+(COLUMN(O14)-COLUMN(Year!$J$14)+1))".
  15. In Cell P14, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P14)-ROW(Year!$J$14))*7+(COLUMN(P14)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P14)-ROW(Year!$J$14))*7+(COLUMN(P14)-COLUMN(Year!$J$14)+1))".
  16. In Cell J15, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(J15)-ROW(Year!$J$14))*7+(COLUMN(J15)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(J15)-ROW(Year!$J$14))*7+(COLUMN(J15)-COLUMN(Year!$J$14)+1))".
  17. In Cell K15, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K15)-ROW(Year!$J$14))*7+(COLUMN(K15)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K15)-ROW(Year!$J$14))*7+(COLUMN(K15)-COLUMN(Year!$J$14)+1))".
  18. In Cell L15, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L15)-ROW(Year!$J$14))*7+(COLUMN(L15)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L15)-ROW(Year!$J$14))*7+(COLUMN(L15)-COLUMN(Year!$J$14)+1))".
  19. In Cell M15, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M15)-ROW(Year!$J$14))*7+(COLUMN(M15)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M15)-ROW(Year!$J$14))*7+(COLUMN(M15)-COLUMN(Year!$J$14)+1))".
  20. In Cell N15, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N15)-ROW(Year!$J$14))*7+(COLUMN(N15)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N15)-ROW(Year!$J$14))*7+(COLUMN(N15)-COLUMN(Year!$J$14)+1))".
  21. In Cell O15, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O15)-ROW(Year!$J$14))*7+(COLUMN(O15)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O15)-ROW(Year!$J$14))*7+(COLUMN(O15)-COLUMN(Year!$J$14)+1))".
  22. In Cell P15, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P15)-ROW(Year!$J$14))*7+(COLUMN(P15)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P15)-ROW(Year!$J$14))*7+(COLUMN(P15)-COLUMN(Year!$J$14)+1))".
  23. In Cell J16, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(J16)-ROW(Year!$J$14))*7+(COLUMN(J16)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(J16)-ROW(Year!$J$14))*7+(COLUMN(J16)-COLUMN(Year!$J$14)+1))".
  24. In Cell K16, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K16)-ROW(Year!$J$14))*7+(COLUMN(K16)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K16)-ROW(Year!$J$14))*7+(COLUMN(K16)-COLUMN(Year!$J$14)+1))".
  25. In Cell L16, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L16)-ROW(Year!$J$14))*7+(COLUMN(L16)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L16)-ROW(Year!$J$14))*7+(COLUMN(L16)-COLUMN(Year!$J$14)+1))".
  26. In Cell M16, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M16)-ROW(Year!$J$14))*7+(COLUMN(M16)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M16)-ROW(Year!$J$14))*7+(COLUMN(M16)-COLUMN(Year!$J$14)+1))".
  27. In Cell N16, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N16)-ROW(Year!$J$14))*7+(COLUMN(N16)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N16)-ROW(Year!$J$14))*7+(COLUMN(N16)-COLUMN(Year!$J$14)+1))".
  28. In Cell O16, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O16)-ROW(Year!$J$14))*7+(COLUMN(O16)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O16)-ROW(Year!$J$14))*7+(COLUMN(O16)-COLUMN(Year!$J$14)+1))".
  29. In Cell P16, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P16)-ROW(Year!$J$14))*7+(COLUMN(P16)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P16)-ROW(Year!$J$14))*7+(COLUMN(P16)-COLUMN(Year!$J$14)+1))".
  30. In Cell J17, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(J17)-ROW(Year!$J$14))*7+(COLUMN(J17)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(J17)-ROW(Year!$J$14))*7+(COLUMN(J17)-COLUMN(Year!$J$14)+1))".
  31. In Cell K17, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K17)-ROW(Year!$J$14))*7+(COLUMN(K17)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K17)-ROW(Year!$J$14))*7+(COLUMN(K17)-COLUMN(Year!$J$14)+1))".
  32. In Cell L17, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L17)-ROW(Year!$J$14))*7+(COLUMN(L17)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L17)-ROW(Year!$J$14))*7+(COLUMN(L17)-COLUMN(Year!$J$14)+1))".
  33. In Cell M17, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M17)-ROW(Year!$J$14))*7+(COLUMN(M17)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M17)-ROW(Year!$J$14))*7+(COLUMN(M17)-COLUMN(Year!$J$14)+1))".
  34. In Cell N17, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N17)-ROW(Year!$J$14))*7+(COLUMN(N17)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N17)-ROW(Year!$J$14))*7+(COLUMN(N17)-COLUMN(Year!$J$14)+1))".
  35. In Cell O17, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O17)-ROW(Year!$J$14))*7+(COLUMN(O17)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O17)-ROW(Year!$J$14))*7+(COLUMN(O17)-COLUMN(Year!$J$14)+1))".
  36. In Cell P17, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P17)-ROW(Year!$J$14))*7+(COLUMN(P17)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P17)-ROW(Year!$J$14))*7+(COLUMN(P17)-COLUMN(Year!$J$14)+1))".
  37. In Cell J18, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(J18)-ROW(Year!$J$14))*7+(COLUMN(J18)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(J18)-ROW(Year!$J$14))*7+(COLUMN(J18)-COLUMN(Year!$J$14)+1))".
  38. In Cell K18, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K18)-ROW(Year!$J$14))*7+(COLUMN(K18)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K18)-ROW(Year!$J$14))*7+(COLUMN(K18)-COLUMN(Year!$J$14)+1))".
  39. In Cell L18, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L18)-ROW(Year!$J$14))*7+(COLUMN(L18)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L18)-ROW(Year!$J$14))*7+(COLUMN(L18)-COLUMN(Year!$J$14)+1))".
  40. In Cell M18, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M18)-ROW(Year!$J$14))*7+(COLUMN(M18)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M18)-ROW(Year!$J$14))*7+(COLUMN(M18)-COLUMN(Year!$J$14)+1))".
  41. In Cell N18, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N18)-ROW(Year!$J$14))*7+(COLUMN(N18)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N18)-ROW(Year!$J$14))*7+(COLUMN(N18)-COLUMN(Year!$J$14)+1))".
  42. In Cell O18, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O18)-ROW(Year!$J$14))*7+(COLUMN(O18)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O18)-ROW(Year!$J$14))*7+(COLUMN(O18)-COLUMN(Year!$J$14)+1))".
  43. In Cell P18, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P18)-ROW(Year!$J$14))*7+(COLUMN(P18)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P18)-ROW(Year!$J$14))*7+(COLUMN(P18)-COLUMN(Year!$J$14)+1))".
  44. In Cell J19, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(J19)-ROW(Year!$J$14))*7+(COLUMN(J19)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(J19)-ROW(Year!$J$14))*7+(COLUMN(J19)-COLUMN(Year!$J$14)+1))".
  45. In Cell K19, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K19)-ROW(Year!$J$14))*7+(COLUMN(K19)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(K19)-ROW(Year!$J$14))*7+(COLUMN(K19)-COLUMN(Year!$J$14)+1))".
  46. In Cell L19, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L19)-ROW(Year!$J$14))*7+(COLUMN(L19)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(L19)-ROW(Year!$J$14))*7+(COLUMN(L19)-COLUMN(Year!$J$14)+1))".
  47. In Cell M19, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M19)-ROW(Year!$J$14))*7+(COLUMN(M19)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(M19)-ROW(Year!$J$14))*7+(COLUMN(M19)-COLUMN(Year!$J$14)+1))".
  48. In Cell N19, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N19)-ROW(Year!$J$14))*7+(COLUMN(N19)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(N19)-ROW(Year!$J$14))*7+(COLUMN(N19)-COLUMN(Year!$J$14)+1))".
  49. In Cell O19, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O19)-ROW(Year!$J$14))*7+(COLUMN(O19)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(O19)-ROW(Year!$J$14))*7+(COLUMN(O19)-COLUMN(Year!$J$14)+1))".
  50. In Cell P19, type "=IF(MONTH(Year!$J$11)<>MONTH(Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P19)-ROW(Year!$J$14))*7+(COLUMN(P19)-COLUMN(Year!$J$14)+1)),"",Year!$J$11-(WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))-IF((WEEKDAY(Year!$J$11,1)-(Year!$P$4-1))<=0,7,0)+(ROW(P19)-ROW(Year!$J$14))*7+(COLUMN(P19)-COLUMN(Year!$J$14)+1))".

March to December Formulas. 

For the rest of the months, the formulas are very similar to February formulas, except the cell references are different. Steps 2 to 8 are exactly the same for these next months.

Steps 1 and steps 10 to 50 are different when it comes to the cell references. Those cell references are all listed below.

  1. March - R11 to X19.
  2. April - B22 to H30.
  3. May - J22 to P30.
  4. June - R22 to X30.
  5. July - B33 to H41.
  6. August - J33 to P41.
  7. September - R33 to X41.
  8. October - B44 to H52.
  9. November - J44 to P52.
  10. December - R44 to X52.

By this point, you should have the Calendar Set Up, Calendar Year, Calendar Title, and Annual Calendar all created. Up next, we'll show you how to create the Monthly Calendars, Important Dates List, and Conditional Formatting for those important Dates.

Don't worry if you don't have the time to complete this tutorial or if this is too complex, this Calendar and more are all available on our shop. Until next time, Happy Planning.


Comments

Post a Comment