Lesson#31: Current dynamic calendar for whole Year

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.

I did modify the format like this shown in the picture.

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)

On the 1st row x 1st column I have changed the formula with =IFERROR(IF(WEEKDAY(DATE($P$2,MONTH(B4),1))=1,1,””),””) 
Next cells of the 1st row formula modified with =IFERROR(IF(B6=””,IF(WEEKDAY(DATE($P$2,MONTH(B4),1))=2,1,””),B6+1),””) 
up to =IFERROR(IF(G6=””,IF(WEEKDAY(DATE($P$2,MONTH(B4),1))=7,1,””),G6+1), “”)
From the next row Sundays will be modified with this formula =IFERROR(IF(MONTH(DATE($P$2,MONTH(B4),H6)+1)=MONTH(B4), H6+1, “”), “”) 

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.


Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

Tagged with: , , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *