Creating Dynamic Calendar in Worksheet (No Macro) - Simple and Easy

hello everyone welcome back equalizing the little apps my name is Dilip in this video we will learn about how to create a dynamic calendar in Excel worksheet here you can see that I have already created a calendar which is totally dynamic and I have used only Excel basic Excel functions I have not utilized any macro code in creating these and new advanced excel functions just simple basic function and some formatting and that's it okay and this calendar is new dynamic so you here you can enter any of the you know year and you can select any of the month for that but brand a dispersive here and this calendar will not be automatically change the basis on the given value in this particular cell like this one over here and this is for you know months so let's enter 2019 and as I am recording this video in May 2009 19 and that today date is 4th of May 2019 so let's select me ok so now you can see that it's a it's this calendar has started showing the calendar for me 2019 right and today's as the 4th of May and today's Saturday so you can see that Saturday has been highlighted in certain certainly has been highlighted with a blue color right so this is a dynamic and you can use this calendar whenever you need to prepare a calendar on worksheet and I want to showcase some value or highlight some dates in your reports then you can use it and gives it this dynamic calendar right and it's very very easy to create this calendar so let's let's create this calendar from scratch to do this just press ctrl and right and say this workbook to save this just press f12 and just give the name as dynamic calendar in work sheet exercise right now just rename the she does 9mm relevant calendar okay just go to view tab and remove the gridlines right and now we as we need to uh create seven a basically a table for seven rows and seven columns so let's select it from you know D seven basically from here so d7 to j7 and let's go to Home tab basically and apply the border all border okay now just start writing the Monday that's caps lock on Monday Tuesday Saturday and right now select the select the range from d7 to no j7 and go to Home tab and then select the font as copperplate so copperplate you know so note the bold one just like the lighter one right make it a Center lined middle align right just click on bold and phone sides should be thirteen so that's increase the font size even thirteen right and fill color should be gold accent this one okay so now we have successfully created you know our on the header part of the calendar that is known now select the range like columns basically all columns from D D to in shape right click on and wait and sit as column widths for 8.43 that's a default just keep it relative now select the row so from 7 to 14 right click on it and row height should be 24 okay now click on OK right and as we have created the the header part and and the calendar is ready all the sails let's move to a previous calendar right and now we need to create this one for to enter the year as well as to select the month here right so let's move to exercise she worksheet bathing it and let's minimize the size of this particular row right now merged the cell d5 to e5 go to Home tab and click on merge and center ok and again select the f f5 to G H pipe and go to Home tab and then click on merge and center right now select both both drains like D from D pipe to H pipe and go to like Home tab select the font as century century right and size should be 12 I make it center aligned and middle line I select the select the font like fill color as gold accent for lighter 80% right now enter the value here 2019 and now we need to get the drop down here so before creating a drop down let's apply border so just select the range control one and just select the color as you know blue and click the double line okay all right now select the cell from f5 to h5 that is already merged again press control one and no just select this one dotted line and select the color as black and apply the left sidebar oh right so we need we have created this one let's keep the drop down to create the drop down we need to know give the reference like we need to write down month from Jan to December January okay and let's use fill handle to fill from Jan to December okay no it's done now let's go to select the f5 press press alt d' have once you press the alt d l shortcut key then it will open the data validation window right in data validation window just click on settings and in allow drop down just like the list and in source just provide the ranges where our a month months are available so it is available in from b7 to and be 18 okay now click on OK ok so now you can see that it has started showing so let's let me eyes up now right and now we need to generate the date basically what is date first date of selected ones so to do that you just apply the formula that is date right and in that date you can see that there are three parameters which is required first one is here the second one is month and the third one is date data basically so let's for here just give the reference of this cell and just press f4 to make it you know absolute reference right now put the comma now we need to find the month basically right so to find the month let's match let's use the match function okay and lookup value select this one the selected month me again press f4 to make it you know a fixed absolute reference right and just put coma and we need to provide the lookup array so for Luka very just select the month from like b7 to be 18 right and again just press it like to press f4 to make it absolute and now risk a coma and then 0 it means we need to exact match okay so now we have passed a year and the month number okay and let's put comma and enter one as a day right close the bracket and press Enter okay so now you can see that you know it has started showing ah the number basically the date in this particular cell let's click on J and we need to apply a weekday function right so weekday and just select the weekday and start bracket and then provide the reference so we need to pass is our date in this particular vector function then just like this one that is i-5 which is date and press f4 to make it absolute reference right and just put comma and you can see that there are a lot of you know a return type that is one two three so we need to select you know three as a reference now click on so once you click on I know like so this formula gives the output s 2 so now we need to utilize this number while calculating the day of the month so let's select the d8 press equal sign and select the date again press f4 to make it absolute reference and then - this one - right and f4 so closed so now you can see that you know it's showing 29th April 2019 now click on like select the e8 like press ENTER and select the reference put this sill and then plus one okay now just drag this and press ctrl R to fill this same formula and control D to fill the same formula right ok now select like this these are the blank cell basically so we need to provide a formula so the formula for this one it should be the Sunday plus one the last date for this week plus 1 right and fill the same formula below press control D right select all the cells from starting from d da - just 13 and press control 1 right go to number and just to give the stern formatting style as you know only D D so we need to show case only and date not the entire dates like full date ok so just press D D and click OK so now you can see that it has started reflecting and the day only now select the entire range from you know d82 and j13 and go to a home tab make it a no center alignment and middle alignment right and font as Botanica bolt mechanic about right and font size should be 12 okay now fill the colour with pick this one this color right and for Saturday and Sunday just change the font font from black to no the blue this one okay now we we need to we have to apply the conditional formatting so that the deeds which do which does it belong to this particular selected month should be a should have no different background like back color as well as the font either font color so select all the sale and go to Home tab then conditional formatting and then new rule okay and in new new formatting rule and just select the use as use a formula to determine which cell to format right and in this just just asked give the reference of d8 basically right and just press f4 like to switch from absolute reference to dynamic reference right and this should be less than this one they did available in I pipe okay so if any of the date is less than this then the color of you know fill color should be white and phone color should be kinda gray right click on OK again okay now you can see that either 29th and 30th you know both the dates are not belonging does not belong to a node and the me and that's why it started showing the discolor okay now we need to apply the condition for meeting for those days which are not the part of me like which belongs to a June so let's select this as well again and go to conditional formatting like Home tab then conditional formatting and then click on new rule right and in uniform new formatting rule will window right just select the use formula to determine which cell to format now click in the apply the formula so let's select a d8 and press f4 to switch from absolute to dynamic range right and press get a greater than sign and mean you to apply a month right a function and of money and a month and we need to UM give the reference of this right and comma 0 so what it will give the end of the month so whatever the month has been selected here this formula will provide the end month right like 31st made 2019 and if any of the date available here is greater than this month this particular date then that should be like the bag back color should be white and the phone color should be you know great so let's apply the formatting fill color select white right and again go to font and in font color select the grey as a font color right click on OK and again okay so now you can see that we have successfully applied you know the formatting and all the things now we need to hide these all these details which are you know which are the required but not for user only for calculation point of view so just select this ok and press ctrl one and in once you press ctrl one then it will open the format cells dialog box in number click on number and go to custom and in custom just provide the type as you know semicolon three times semicolon ok click on OK now you can see that it's a it's not reflecting over here now again do the same for you know i-5 and jpf so just select I 5 + g5 press control one right and again inform itself click on number then custom and provide the formula last 3 semicolon 1 2 3 right 3 semicolon click on OK now you can see that it's not showing now select the you know uh columns from D 5 to sorry D to J and right click on these columns and column weights and just change the column width from like to 8.43 click on OK so now we can see that if we have successfully created this calendar let's select any of the date any of the month basically it's from June now you can see that in it it started showing the and the date for June and for October you can change the you know here as well and let's enter 2020 and select the in January so you can see that it started showing the calendar for January as well so this is you know one of the best way to create a calendar with the simple basic excel formula as well as some formatting hope you enjoyed this video thank you for watching please like share subscribe and comment have a good day bye bye

Channel: TheDataLabs

Views: 49,238

Keywords: Creating Dynamic Calendar in Worksheet, How to create perpetual calendar in Excel, Creating Full Year Calendar in Excel, Automated Calendar in Worksheet, how to make an interactive calendar in excel, how to insert calendar in excel cell, how to make a calendar in excel, insert drop down calendar in excel, dynamic calendar excel, excel calendar formula, create calendar from excel spreadsheet data, simple and easy calendar in excel

Id: CpWHjv5zj2s

Length: 17min 36sec (1056 seconds)

Published: Mon May 06 2019