Dynamic Calendar for 2020 in Excel

hello everyone welcome back you were watching the data laughs my name is Lily in this tutorial we are going to learn how to create a fully dynamic calendar in Excel so here basically we will develop a dynamic calendar it means we are not going to any consider any static value that calendar would totally dynamic and once you will select any of the year from the drop-down then entire calendar will be adjusted from January to December so let's move to excel window and start creating this calendar from scratch so here I have prepared some lists like the month exam to December and the holidays which are falling in 2020 so our mainly focus we will focus on or 2014 so whenever you think that ok you need you just need to modify the calendar for 2021 or 2022 so you just need to make the adjustment in you know the holy day these are the quality major holidays which are know for 2020 so let's press ctrl n to create a new workbook and press f12 and this would be the designated folder where you want to sift so I am just going to saved at this location so just paste the path over here click on Save button and now a save as type so there is no need to keep as an XLS M so macro enable file so you just need to make it a dot XLS file right and just rename this file I'll give the file name as a dynamic calendar for 2020 ok right and just click on save so now you can see that we have saved this file with dynamic calendar in Excel 2020 right so we need to diff two different worksheet one is for calendar one is for our support file so let's click on new sheet and just rename this support file so we will utilize this support - like picking the creating the drop-down for a month as well as for whole gay list so as we have already prepared the list of holy days and ones just copy this entire data available in sheet 1 control sheet control Z and move to our calendar just press control V okay now move to the previous worksheet where we have kept the holidays now let's press ctrl W closes there is no requirement of this file now let's rename this file so let's calendar ok and just move to support it go to view tab and uncheck the gridlines same goes for calendar to select this calendar worksheet go to view tab and untick the gridlines okay now we need to create the label over here so let's let's make this that's okay go to Home tab and merge and center ok and again in Home tab just fill color should be let's see select gold X and 4 double click on this start writing also let's make it blank eyes up now now let's create the label you know calendar the calendar grid basically so 7 by 7 so we need to consider semi rule and 7 columns which is start from you know b9 to h9 so let's select some v9h 9 ok and this is 1 2 3 4 5 6 7 ok go to Home tab and then all borders so you can see that we have 7 columns over here and semi rows over here which have been highlighted with a border right so let's start writing the knee the DNA basically so let's go Monday Tuesday Wednesday Thursday Friday Saturday Sunday so let make it middle line and sort central line okay now highlight this column header with blue ok this one and font color should be this increase the size font size a little bit more now let's select the entire column from starting from B to X and right-click on that column which should be 6 click on OK ok now make this June at and represent so let's merge this header this one so just select this be a to etch it and then merge and center and then just like the blue dark blue font size font color should be white and font names will be calorie and increase the font size now we need to create the drop down so press alt d l so once you will press the LDL data validation window will appear so in allow section or this particular this drop down just select the list and in source or just put your cursor over here and then click on that and then go to support and then select chance to December this particular aim you can also use on the name over here so I'm utilizing the range just click on OK and now you can see that there is a drop down available over here so let's select January ok now increase the font size maybe 16 yep okay so we need to create the date basically to create the date from you know here to here we need three different inputs one is month number one is the date first date of that particular month and week number so let's find out the date the month number so let's start applying match and what we need to do or we need to look up this value so that's a big be eight and we need to look up this value in the lookup area that is available in the support file so let's select this so this is the lookup area and let's press f4 to freeze this range okay and match type should be exact match so enter zero now close this bracket okay so you can see that for January it has I started reflecting one let's select the tab now - April 4 so it's working right now we need to create the date over here so to create the date let's merge two cell f2 g and merge and center now apply date formula here should be so before creating the date let's create the input urn drop down for user so Majid and give the level as here because he user will select the air and it will all the calendar like the CH and February may March April entire month the date of entire month will get automatically adjusted basis on the selection okay now make it middle Center okay and cell style should be maybe this one and here just merge it and again say lest I should be let's select this or maybe in this one okay and press alt d l so once you'll press the L deal in the same same way we created the list for month we just need to create the list for a year so in data validation window just select allow section that should be our list value and here we need to write so let's start with 10 M 2 0 1 9 - 0 - 0 - 0 - 1 - 0 - 2 - 0 - 3 - 0 - 4 so let's keep at 2:02 for like 2024 Cal 2024 so you can eat add as many as 8 years you want just put coma ovary and start writing all those years over here right let's click on OK ok now select the value so let's like 2020 so let's create the header first so header should be dynamic dynamic calendar for composition and select this particular cell press f4 to freeze it now you can see that it had started showing dynamic calendar for 2020 they did make it middle end and increase the font size okay let's select the one year so once you select the 2021 here you can see that it has started showing dynamic calendar for 2020 that's right 20 to the 20 so it's working right now we have here which like user has already selected we have identified the month number now we need to identify the date so we will utilize the date formula and we're for year we will select this range w5 frigate and for month we have already lets me keep as a d7 and the one is a date okay so now you can see that it has started showing first January 2020 okay now we need to identify the big day basically on every 1st of like 1st January 2020 what would be the weekday basically so let's start utilizing the phone a weekday okay and the serial number it means we need to pass the date so let's pass this state comma and the return type so basically the return type should be three so let's say three it means if it would be Monday then it will return 0 if would be Tuesday then it will return 2 and so on okay so now you can see that it is showing two it means first Jan 2020 would be Wednesday make it center line middle end okay now let's start putting the formula for each and every day here so equal to this the date - this one okay so let's enter the formula for Tuesday and so on so just select this cell make press equal to and give the select this this value plus one okay now select this and press select the entire range from C to H and press ctrl R okay and here we need to start the formula so this value the Sunday plus one okay and again this value should be this plus one okay so select this range and press control-d now you can see that this is not showing basically so press control one while selecting the entire range and good custom and as we only need to show the day not month and here so press D D okay so now we can see that it has started showing the day some of the days are not belonging to this particular month like 30 30th 31st are not from this month because it's coming from the previous one and again here you can see that this particular this these are not coming from this month let's select this entire range and start replicating for the month February March April to December okay so leave a one column blank over here just press and I can copy it again leave one column blank over here so let's select this February now you can see that it has been automatically adjusted and this is for March can now select the entire range from here to here okay and leave one row blank so in row number 70 we just need to paste the values okay and again leave one row blank here this one 26 so let's paste is from rule number 27 so we have covered nine months rest thing are still pending so let's keep 36 rows number blank and pitch the value here from 37 okay so let's move on talk Jan break every March let's select this for April this is for me this is for June let's scroll this in July it was September okay this should be October this should be remember and this should be December so now you can see that it has started reflecting the month basically month each and every date for that particular month okay so let's hide all these values which are available on top of every calendar over here select this entire range okay again go to the bottom select the entire range over here okay because these are the support cells we do not need to show these values to user now press ctrl one and inform itself window just go to custom and replace the general width or three semicolon okay now you can see that all the values have been disappeared now so let's apply the conditional formatting or to hide all those values the dates basically which are not associated with the given calendar so for January we need to hide all those dates which are coming from the number as well as which are belonging to the next month that is February so just select all those dates go to Home tab and then in Style section just click on conditional formatting and a new rule okay just a select the user formula to determine which cell to form it and now enter equal sign and then start entering formula text and select the first start date that is you know b10 and make it totally relative so no need to freeze the column as well as row comma start like inverted commas then press for time n then okay now press equal to and then give this this one select the January okay if both are equal to like the month name for this particular date and the selected month both are equal then we need to apply the conditional formatting otherwise no we need to just keep as it is right so here we need not equal to basically so we are going to hide all the digits which are not belonging to this particular month okay click on format and on the font should be font color let's the selector agree and fill should be no ill the may be white so basically white this one okay click on okay let's select this range go to Home tab and apply the color coding over here maybe this one the gold accent for lighter over 10 percent so let's first of all let's fill all those calendar with the same color let's quickly apply the the conditional formatting on each and every month to hide the dates which are not belonging to that particular month so we will use the same technique so let me show you again the same technique for February and then I will repeat this at all those activities for March April May June July / September October number and December right so let's select this entire range go to Home tab now conditional formatting okay this is click on new rule and in the new formatting rule just select the use if formula to determine which cell to format here we just need to start writing the formula and the formula would be text and in text function we need to pass the first date of each and every month the calendar so basically here the first date is 27 so let's select this particular cell and unfreeze the column and row make it relative coma start inverted commas okay press 5 time M for time M basically and close the bracket then not equal to sign and select this one this particular moment okay now freezes okay form it so we need to unfreeze okay format let's copy this formula so that we can utilize for others format and you know font color this one gray fill that should be white equal to okay so let's replicate the stream you you [Music] so now you can see that we have applied the conditional formatting to make it a little bit visible for each and every date which are not associated with the dates are for that particular month so it's all about creating a dynamic calendar with the help of excel formula so let's change the month so here over here now once you'll change them here you can see that entire and free thing has been interested bases on the selection I hope you will find this video as useful thank you for watching please like share subscribe and comment have a great day bye bye

Channel: TheDataLabs

Views: 16,434

Keywords: Fully Dynamic Calendar in Excel, 2020 Calendar in Excel, Entire Year Calendar in Excel, 2020 calendar template in Excel, Printable calendar for 2020 in Excel, 2020 calendar excel download, free excel calendar 2020, how to make an interactive calendar in excel, how to make a calendar in excel 2007, create calendar from excel spreadsheet data, how to create a yearly calendar in excel, excel calendar 2020, dynamic calendar excel, How to make an automatic calendar in Excel

Id: mWk_BEapPjs

Length: 22min 41sec (1361 seconds)

Published: Tue Dec 31 2019