How to create this interactive calender using Excel Formulas??
- with buttons to choose year and month.
Answer : Download File
If A1= Month Number (1-12) And A2= Year
Then use these formulas:
D12=IF(WEEKDAY(DATE(A1,A2,1))=1,1,"")
E12=IF(WEEKDAY(DATE(A1,A2,1))=2,1,IF(ISERROR(D12+1),"",D12+1))
F12=IF(WEEKDAY(DATE(A1,A2,1))=3,1,IF(ISERROR(E12+1),"",E12+1))
G12=IF(WEEKDAY(DATE(A1,A2,1))=4,1,IF(ISERROR(F12+1),"",F12+1))
H12=IF(WEEKDAY(DATE(A1,A2,1))=5,1,IF(ISERROR(G12+1),"",G12+1))
I12=IF(WEEKDAY(DATE(A1,A2,1))=6,1,IF(ISERROR(H12+1),"",H12+1))
J12=IF(WEEKDAY(DATE(A1,A2,1))=7,1,IF(ISERROR(I12+1),"",I12+1))
D13=J12+1
E13=D13+1
F13=E13+1
G13=F13+1
H13=G13+1
I13=H13+1
J13=I13+1
E12=IF(WEEKDAY(DATE(A1,A2,1))=2,1,IF(ISERROR(D12+1),"",D12+1))
F12=IF(WEEKDAY(DATE(A1,A2,1))=3,1,IF(ISERROR(E12+1),"",E12+1))
G12=IF(WEEKDAY(DATE(A1,A2,1))=4,1,IF(ISERROR(F12+1),"",F12+1))
H12=IF(WEEKDAY(DATE(A1,A2,1))=5,1,IF(ISERROR(G12+1),"",G12+1))
I12=IF(WEEKDAY(DATE(A1,A2,1))=6,1,IF(ISERROR(H12+1),"",H12+1))
J12=IF(WEEKDAY(DATE(A1,A2,1))=7,1,IF(ISERROR(I12+1),"",I12+1))
D13=J12+1
E13=D13+1
F13=E13+1
G13=F13+1
H13=G13+1
I13=H13+1
J13=I13+1
F16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,E16),E16+1,""),"")
G16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,F16),F16+1,""),"")
H16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,G16),G16+1,""),"")
I16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,H16),H16+1,""),"")
J16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,I16),I16+1,""),"")
D17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,J16),J16+1,""),"")
E17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,D17),D17+1,""),"")
F17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,E17),E17+1,""),"")
G17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,F17),F17+1,""),"")
H17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,G17),G17+1,""),"")
I17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,H17),H17+1,""),"")
J17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,I17),I17+1,""),"")
G16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,F16),F16+1,""),"")
H16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,G16),G16+1,""),"")
I16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,H16),H16+1,""),"")
J16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,I16),I16+1,""),"")
D17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,J16),J16+1,""),"")
E17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,D17),D17+1,""),"")
F17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,E17),E17+1,""),"")
G17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,F17),F17+1,""),"")
H17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,G17),G17+1,""),"")
I17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,H17),H17+1,""),"")
J17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,I17),I17+1,""),"")
D14=J13+1
E14=D14+1
F14=E14+1
G14=F14+1
H14=G14+1
I14=H14+1
J14=I14+1
D15=J14+1
E15=D15+1
F15=E15+1
G15=F15+1
H15=G15+1
I15=H15+1
J15=I15+1
D16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,J15),J15+1,""),"")
E16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,D16),D16+1,""),"")
E14=D14+1
F14=E14+1
G14=F14+1
H14=G14+1
I14=H14+1
J14=I14+1
D15=J14+1
E15=D15+1
F15=E15+1
G15=F15+1
H15=G15+1
I15=H15+1
J15=I15+1
D16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,J15),J15+1,""),"")
E16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,D16),D16+1,""),"")
And the calender is Ready.