This post is continuation for one of my previous posts Lesson#27: How to make a calendar in excel without VBA. I will show how to modify this calendar a little more and make it a current dynamic calendar for the whole year.
Below MONTH I have put the formula =MONTH(TODAY()) for showing the current month.
Below YEAR I have put the formula =YEAR(TODAY()) for showing the current year.
For the month heading I have put the formula
=DATE($P$2, 1,1) for January
=DATE($P$2, 2,1) for February and up to December by adding this formula =DATE($P$2, 12,1) (number in Red Color is to be changed depending upon the month)
From the next row other than Sunday, means Monday to Saturday will be modified with this formula =IFERROR(IF(MONTH(DATE($P$2,MONTH(B4),B7)+1)=MONTH(B4), B7+1, “”), “”)
I will refer my previous post Lesson#27: How to make a calendar in excel without VBA for explaining the logic.
I have added another feature by using Conditional Formatting which highlights the day of current date.
By selecting a whole month I added this formula =DATE($P$2,MONTH($B$4),B6)=TODAY() in Conditional Formatting.
Download link given in below for the file.