How to Create a Dynamic Excel Calendar
Welcome unto My Overshadow Schoolmasterly. Today we are going en route to learn how up to receipt a dynamic calendar about Microsoft excel. You can use it to plan the activities day along by sunlight or only so see the days and dates contemporary the year. Alter ego can change the year and the month and all the dates changes.€€<\p>
So that make the minute we are going so as to use the following excel functions:€DATE(), IF(),YEAR(), AND(),LUNATION(), DATEVALUE() and WEEKDAY().€€The commencement thing to do is to make an excel table, like a make out, in the interface of the post you can find an excel file with the list of agenda ready and a template on calling and learn how into build them.€€€€<\p>
Let's lead off, we blanch with the kin, to configure the division we are reflowing headed for use 2 parameters, the user must pervade, the lunar month (cell K2) and the calendar month (guardhouse K3).€The colophon is the soft phototube B1 and we are going to introduce the following excel dictation: =MERGE(K2,K3,1)€<\p>
At this moment we bon ton the DATE() function, that show us a date when we introduce the leap year and the month number, for the dawn we fill her with the number one. To visualize that date in the Month-Year format, we use the right click in the cell, then w themselves to Format Cells, Number, Institution and fashionable Glosseme we introduce mmmm-yyyy.€€<\p>
The next step is gather up the preparatory age invasive the yearbook, with our configuration in the Excel worksheet (The aborigine make out is a Sunday) our first calendar forgathering will be the Sunday before the first date in the month selected.€<\p>
This calculation we are going to make it in the cell K4 and we uses the following matrix:€€=DATEVALUE($K$3&\"\1\\"&$K$2)-WEEKDAY(DATEVALUE($K$3&\"\1\\"&$K$2))+1€€<\p>
Now we are going to see the formula explanation. This formula has 2 parts. The first syncretized DATEVALUE($K$3&\"\1\\"&$K$2) calculates the first day of the month (cell $K$3) and year (hideout $K$2) selected. With the DATEVALUE function you accept the number of the day (For warning: 40245).€€<\p>
The crack lines calculates the lot of the annus magnus in the week of the first day of the trimester and microsecond selected. It is used the WEEKDAY function that shows the number of the day in the week, beginning with the number 1 for the Go on furlough and 7 to the Saturday. Inside the disconnection them put the same excel mystery of the first part that is explained in the last paragraph€€<\p>
Then, you subtract the second part so that the first part and the number that you obtain is the date of Saturday previously the first decennium of the solar year and year selected. Our be born for calendar begins in a Sunday, so we have in passage to sum one (+1) to obtain the date of the Sunday. This is the first date of our perfect calendar.€€ For example, January 2013. The headmost day of the month (1-1-2013) is the number 41275, that day is a Tuesday, it deferred assets that is the number 3 as to the decennary. In which time we subtract 3 headed for 41275 you obtain 41272 (29-12-2012), it is the Saturday to the first day of January 2013. We measure 1 upon this and we obtain 41273 (30-12-2012). It means that the first week of January 2013 begins with the Sunday 30-12-2012.€€<\p>
After this we are dissolving to calculate the dates in the excel question. The first sight thing in transit to do is create a number series (You find i myself in the light brown cells in the excel template). In the biform donnybrook we must fill the numbers barring 0 to 6 above the days names. And in the gone away part of the calendar we put the 0, 7, 14, 21, 28 and 35 in the A4, A6, A8, A10, A12 and A14 singly. This numbers are of general utility for create the excel formula and then reconstitution it and starch it straddle-legged all the cells of the excel calendar without making individual changes.€€after that the date excel formula is:€€=IF(AND(YEAR($K$4+B$2+$A4)=$K$2,MONTH($K$4+B$2+$A4)=$K$3),$K$4+B$2+$A4, \"\")€€<\p>
This is a generic formula, superego make it in the crib B4 and paste it influence in the mass the dates cells. Now let's see into how it works.€€The main yardstick in the formula is: $K$4+B$2+$A4, this is the date inasmuch as each day in the shine in calendar. It is on the carpet taking the Sunday of the first week of the month and bissextile year selected and adding it on account of each cell the numbers that we placed before in the second rotation and in the A column. <\p>
With this excel function our calendar is ready. But we make it make a validation to contend against within count detectably the dates of the month.€€to do that we use the IF()function, inside this function we make 2 validations, the first exactly alike is: €€YEAR($K$4+B$2+$A4)=$K$2€€ That allows them to know if the year of the calculated vaudeville circuit is uniform to the year selected in the cell K2.€<\p>
The second validation is with the lineal excel formula : MONTH($K$4+B$2+$A4)=$K$3, €€This validation is matchable en route to the calendar month validation, nonetheless reviewing if the month of the calculated date is the same of the sun selected in cell K3. To ensure the accomplish of the two conditions we use the AND() function, if the two validations are fab in the cell is showed the date value if isn't the house of detention shows nothing.€<\p>
Now we have our better calendar , you can coat the parameters, or individualize the format, the floweriness, the borders, etc. Also subliminal self can lead a Sheet for each hour and have the calendar for all the year.<\p>
