HOW TO CREATE DYNAMIC CALENDAR IN EXCEL

hello friends my name is pre Andrew Kumar and welcome to my youtube channel friends today we will learn how to create a calendar in Microsoft Excel without the using of any macro earth and ubv pudding so as you are seeing I have created a calendar with two drop-down boxes one is for the month and one is for D here and if I will change the month or I will change the year the calendar will be change accordingly so let's start I'm going to see - and we create the new calendars on the seat - so I am going in the Developer tab into the insert and from form control I will drag a combo box and we can make another copy of this combo box one is for these month and second is for the year so I am just copying it and pasting it here so I've created two combo box and I will create the list for it for the month so I am going to the left and I am dieting be January and I can't I get up to the December and for the year I am taking mm ticking K+ bond or I can use the Alt key is for the data series directly and can e I s and we can put the pun then take data series will be created there and I'm also doing this going to the former's control and giving the input range will be from a box burn so input range will be the month here to here and the link cell I am giving for the output I am giving it a bun so and similarly for the second combo box and giving the input range for the year so here I can drive it from q1 to the last here and sailing will be the a - okay a - okay so now top down is available and 2/3 so on column B I will get the exact number what has been selected so I will use the index formula here index P - P comma a - similarly index Q - Q comma 8 - so whatever has been selected the data will be focusing in the V 1 for the combo box burn V 2 for the combo box - let us make it best fit okay so now drop down is working and we are getting the values there so now let's start the making the calendar I'm I'm creating a matrix saving by 6 matrix saving columns and 6 rows so I'm just creating a white area and I'm putting the day's name basically so they will start from the Sunday to Saturday ok and insulting 1 and merging it and we are putting the date value formula and creator date here for the past - and the month from there and - and the ear from there so we will get the first date of the June 2009 here as the month and date is here there so we are going to the custom format and we will focus your month complete name mm mm and the year complete for 10 by so we are getting the month name now yes so whatever we will change the gate 2016 so I will get the 2018 or do something else than the similarly and we can change the month oh so we are getting a one row here so actually we have inserted a role here that is why the reference has been changed now we have to change the link value absolute input range basically so now we are taking it again P 1 to P 12 okay okay fine now swapping okay now we have to put the formula in the for the first row or the percent of the calendar we'll put the formula if weekday for that first month of first date of the particular month is equal to 1 because Sunday we have not taken any return type so be by default the first day of the week will be certain day so we can take it either one or we can put another formula so that we don't need to type in the next cell 234 something I get oh I'm using your the columns so columns I am taking the three two three eight sorry C eight to see eight right and I am just locking the phosphate so it builds it it will give you the result you know one because there is a one column only so then it will return the C seven that is the first date of the month otherwise it will showcase be blank so I just feel light on the Monday but on the Monday there is one more condition that the possibility we have we can we get the date only son in the first day on the Sunday so we are taking the if Sunday is not equal to blank then the Monday will be the Sunday plus one and similarly fill write the formula tilde Saturday for the first row of the calendar and on the second row I will put the simple formula the last date Saturday date plus one and we can fill right here and same formula we can copy here okay there is change actually on T column we'll take the plus one see ten plus one and fill light copy the formula and drag till b4 through so on the first row we'll use the different formula and the second row third row and the force of the formula will be same the another formula and on the fifth and sixth load there will be another formula so on this six fifth row I am using the end of the month I will check the end of the month maybe put there is the page even a possibility if we are creating a calendar for the fab so maybe on I to an could be the 28th so that would be the end of the formula the guide should be the blank so that is why I'm checking the end of the month is greater than I 12 then I 12 plus 1 as the plank so similarly am filling it right I will just change the formula in place of j2l I will take this 315 now C 13 so C 13 okay and just will fill it right till the end and just copy this formula based in this one so we will use the three formula basically on the first row there will be another formula on the second third fourth row is another formula the same book and on the fifth and six rows this will be the air the phone so let's format it according to our calendar format okay so now calendar is City actually so we have put the order formula now G we just have to make it format so we can change the point of the month and the color so we taken I think the color the blue color and the background will be devoid here we can take it the another color the background we can take it white and so we have to remove the border actually so we cannot take T blank cells water so we can take the water for the half of ourselves because there would not be chance of plan and the in the end we can use the conditional formatting oiled OD and the noodle go to the only cells and select cell value in place of cell value no blank and the format just take this border and I remember we have taken the water color yellow or the another color and Phil will take the black background with the white point let's make it bold okay like that let's create same from it here there is just will de color and make the font white and change the border also will be the same color so water color this one multi ok now form it is Sadie the same format which you have we have taken on the feet one so let's make the color for the Sundays so same form it and we'll take the colors for the Sunday the darker to the magenta and not the same format now at clipping the same almost calendar is done outside the extra rows and the columns so I am just hiding it J - so go to the k2 and and be hide hide this No similarly that's hidden the headings and the gridlines and extra rows also to be right okay house has been hidden now the calendar is they do basically so let's make this alignment now it has been ad absolutely the same calendar which we have created on the seat wall so you also can create it with the using of the three formulas only so in this render basically we have love the again the EO month and d-day and the beak day basically so they did when you

Channel: PK: An Excel Expert

Views: 100,679

Rating: 4.5225048 out of 5

Keywords: EXCEL, ADVANCE EXCEL, VBA, MACRO, How to create calendar in excel, Formcontrol, Date and time function, Combo box, EOMONTH, Dynamic Calendra in Excel, beatiful calendar in excel, Calender in Excel for 20 Years

Id: blFBK93q9cM

Length: 11min 52sec (712 seconds)

Published: Thu Mar 23 2017