To build a custom calendar in EXCEL 2013 -2014, proceed as follows:
1) Let's start writing the first day of January 2013 to 2014 in a box and we format the date so as to come out even on the day of the week to which it refers, in this case Tuesday.
2) Now we copy down for at least three weeks in order to get just the days of the first three weeks, and color of green on Saturday and Sunday.
3) On the column "E", write the abbreviated day of the week corresponding, Mon, Tue, Wed, Thu, Fri, Sat, Sun ..
4) On the "C" column, instead of writing the number of the week, ie from Monday to Sunday is considered a week, next to which we write the number of the week progressive corresponding 1, 2, 3, as in this case.
5) In fact when you go to the Monday of week 3, we do not write the number, but a formula that indicates the box above + 1, so as to show a number next to that of the previous week.
6) In the next box, instead we indicate the reference to the text box above and just in order to keep up with the week number of the same.
7) Now we can select the entire 3 week from Monday to Sunday, and position the mouse over the bottom right, click and drag down to copy both the sequence of the day, both the formatting and also the progressive weeks, all in one fell swoop.
8) Now we got the whole 2013 calendar vertically.
9) In the column "B", now we write the formula = C3 & E3, so as to unite the week number and the corresponding day (this will be useful later).
10) Now we create another table where we want to list all dates of 2013 on Monday, to do this we construct a table with 53 numbers from the left, and write a formula CERCAVERT, we find all the Mondays in 2013 by extracting from columns C and D.
Then we write = VLOOKUP (G5; C: D; 2; O), and we copy the bottom.
11) Now prepare the table to the right of our custom calendar, for example that develops in three blocks, one under the other.
12) Now we insert a control card by the arrows DEVELOPMENT - INSERT
13) The parameters for the command will be swept, as values from 1 to 50 and H3 as a box above the list of the weeks in 2013.
14) Now we prepare the formulas of the three blocks of the calendar, first thing in the first box on the first block, we just H3 as a reference, ie the selected week with the control arrows.
15) Instead, the box beside write a formula that refers to the box on the left + 1, so as to continue the progressive weeks.
16) But in the block below will refer to the last box above + 1, to continue even under the progressive weeks.
17) Now select all the boxes and we format as DATE, but custom, that is "dd mm", that is the date we want to see only the day and month.
18) Now we write the formula to populate our calendar, that we will in the first box, on Monday of week 2, and will be = VLOOKUP ($ K6 & L $ 5, $ B: $ D, 3, 0), the $ serve to make the references of the formula copiable also in other cells.
19) Then we copy the formula to all cells, even in the block below, where, however, we need to correct the reference to the seventh (in blue), which can drag down on the corresponding week 7, or make the correction directly by changing the reference in the formula L5 to L14.
20) Now our personalized calendar is ready, just click on the arrows to control and change the weeks and also every day in the three blocks.
21) We can go forward or backward with the arrow and have a rather wide of the days, for example, if we run with the control arrows to make sure that the current week matches the one in yellow, we can see no less than 5 weeks prior to and 9 weeks later.
This, as usual, is just an example, applying the same logic you can make customized calendars for every coming year, reflecting the important thing that we want to see when we look at our custom calendar.
