How to Create Excel Calendar for Specific Year with Automatic Date Marker

Hogan's here I will show you how to create a calendar that you can clip every year where you can make the dates change its color automatically based on input that you put in - date tables we can start by creating a table for January the first month of each year after you clear the table you have to put the face of January in correct day and then you can start to increase the dates by using the basic addition formula the formula is applied to the first four cows while for the remaining two rows you have to put a formula that will check the condition of the last 8th of January you and after you finish with the date you can now change the date format to only show the dates values only can be for creating the remaining eleven months you can start to personalize this Faceman to meet your own style now that you have finished personalizing this January table you can start creating the February table by duplicating the January table they can repeat the same steps to create the dates for a double of February as well as much you and repeat the steps on to all 12 months tables are completed the severe panda is ready now if you don't have any intention to add extra events you can start preparing this calendar to print now to add a damn it dynamic death marker you have to create a table face the first table I created here is a fix even tables here you must select the date column and define name for this range now select all dates in the calendar and then open the conditional formatting menu we will try to create a formula to change that color if two criterias is map here I'm using the match function where the logic is if the dates is the same with the death in the range that you specified before then the color will be changed based of the color that you set in this menu after you finish with the first formula of the conditional formatting you can repeat the same step for creating the second conditional formatting corner you now I will show you how to create conditional formatting formula for consecutive dates and different from fixed a table here you have to define a name for each cells with dates as you can see here you select the cell and divine the name you have to do this for all those pencils you after finish defining the last cells you can start to create the conditional formatting formula for the first row of the consecutive th even table we have to be careful on creating the formula in the conditional formatting formula bar and for this formula the data will change base of five condition so you must use the N function so it will combine if any of those criterias are not met the tits color one change you you can see the result and now we can continue to create inform conditional formatting formula for the remaining four routes you because I'm not comfortable to write the formula directly in the condition of the conditional formatting formula bar so I try to copy and pass in some and you sell here and duplicate all the formula to cover all the tits in the one spot if Tate's table you can see and follow the steps that I use here and don't forget to put the auto function in front of this formula you once you complete it on changing the name range you can copy and paste the formula back to the conditional conditional formatting formula you your calendar is finished you can start styling your calendar to meet your own style and after that you can prepare your calendar to be printed you okay that's all guys thank you for watching bye bye you

Channel: Exceltemplate.NET

Views: 284,982

Keywords: 2016 calendar, 2017 calendar, excel calendar, excel tutorial, how to create excel calendar

Id: XPgt0FQ7byA

Length: 11min 3sec (663 seconds)

Published: Thu May 12 2016