Array Formula Calendar only show past rolling year with conditional formatting

[baker_89]

I found this sheet online and I want to alter the calendar to show the past year from current month, our attendance policy absences fall off a year from their occurrence so I would like to show the past year on 1 page instead of jumping back and forth between years if possible. This is what the HTML Creator gave me when pasted....

Excel 2010

	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW
3	Name 2
4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Calendar View​

Worksheet Formulas Cell Formula C9 =DATE($AN$5,ROWS($C$9:C9),1) C10 =DATE($AN$5,ROWS($C$9:C10),1) C11 =DATE($AN$5,ROWS($C$9:C11),1) C12 =DATE($AN$5,ROWS($C$9:C12),1) C13 =DATE($AN$5,ROWS($C$9:C13),1) C14 =DATE($AN$5,ROWS($C$9:C14),1) C15 =DATE($AN$5,ROWS($C$9:C15),1) C16 =DATE($AN$5,ROWS($C$9:C16),1) C17 =DATE($AN$5,ROWS($C$9:C17),1) C18 =DATE($AN$5,ROWS($C$9:C18),1) C19 =DATE($AN$5,ROWS($C$9:C19),1) C20 =DATE($AN$5,ROWS($C$9:C20),1) D26 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5,1,1),tblLeave[End Date],"<"&DATE($AN$5+1,1,1))-T26 L26 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5,1,1),tblLeave[End Date],"<"&DATE($AN$5+1,1,1),tblLeave[Type of Leave],Settings!$D$4) T26 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5,1,1),tblLeave[End Date],"<"&DATE($AN$5+1,1,1),tblLeave[Type of Leave],Settings!$D$5) AB26 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5,1,1),tblLeave[End Date],"<"&DATE($AN$5+1,1,1),tblLeave[Type of Leave],Settings!$D$6) AJ26 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5,1,1),tblLeave[End Date],"<"&DATE($AN$5+1,1,1),tblLeave[Type of Leave],Settings!$D$7) AR26 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5,1,1),tblLeave[End Date],"<"&DATE($AN$5+1,1,1),tblLeave[Type of Leave],Settings!$D$9) D30 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5-1,1,1),tblLeave[End Date],"<"&DATE($AN$5,1,1)) D31 =IFERROR(IF(D26>D30,"UP ", "DOWN ")&TEXT(D26/D30-1,"0%;0%"),"") L30 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5-1,1,1),tblLeave[End Date],"<"&DATE($AN$5,1,1),tblLeave[Type of Leave],Settings!$D$4) L31 =IFERROR(IF(L26>L30,"UP ", "DOWN ")&TEXT(L26/L30-1,"0%;0%"),"") T30 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5-1,1,1),tblLeave[End Date],"<"&DATE($AN$5,1,1),tblLeave[Type of Leave],Settings!$D$5) T31 =IFERROR(IF(#REF!>#REF!,"UP ", "DOWN ")&TEXT(#REF!/#REF!-1,"0%;0%"),"") U31 =IFERROR(IF(T26>T30,"UP ", "DOWN ")&TEXT(T26/T30-1,"0%;0%"),"") AB30 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5-1,1,1),tblLeave[End Date],"<"&DATE($AN$5,1,1),tblLeave[Type of Leave],Settings!$D$6) AB31 =IFERROR(IF(AB26>AB30,"UP ", "DOWN ")&TEXT(AB26/AB30-1,"0%;0%"),"") AJ30 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5-1,1,1),tblLeave[End Date],"<"&DATE($AN$5,1,1),tblLeave[Type of Leave],Settings!$D$6) AJ31 =IFERROR(IF(AJ26>AJ30,"UP ", "DOWN ")&TEXT(AJ26/AJ30-1,"0%;0%"),"") AR30 =SUMIFS(tblLeave[Days],tblLeave[Employee Name],valSelEmployee,tblLeave[Start Date],">="&DATE($AN$5-1,1,1),tblLeave[End Date],"<"&DATE($AN$5,1,1),tblLeave[Type of Leave],Settings!$D$7) AR31 =IFERROR(IF(AR26>AR30,"UP ", "DOWN ")&TEXT(AR26/AR30-1,"0%;0%"),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula H9:AW9 {=calendar} H10:AW10 {=calendar} H11:AW11 {=calendar} H12:AW12 {=calendar} H13:AW13 {=calendar} H14:AW14 {=calendar} H15:AW15 {=calendar} H16:AW16 {=calendar} H17:AW17 {=calendar} H18:AW18 {=calendar} H19:AW19 {=calendar} H20:AW20 {=calendar} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To calendar =daysAndWks + dateOfFirst - WEEKDAY(dateOfFirst,2) valSelEmployee ='Calendar View'!$AN$3 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>