Office hacks: on-demand production of flexible and variable Excel work schedule

Each month starts on the 1st, but the total date of each month is not always the same, there are 30 days or 31 days, and also the 1st of each month does not always happen to be a Monday. For example, if the month-end summary is scheduled on the last day of the month by default, but if the day is closed on Saturday, the plan will need to be changed to Monday or brought forward to Friday. Monthly plan to manually adjust the apparently extremely inconvenient, now with the help of Excel functions, we can easily create a variable Excel (this article to the 2016 version as an example) work schedule to.

First, follow the prompts to create a plan worksheet template, enter the plan year in A1, B1 enter the number of months, C1 enter the “monthly work plan words. Then enter the formula “=IF(DAY(DATE($A$1,$B$1+1,0))<COLUMN(A1),””,COLUMN(A1))” in C3, and enter the formula “=IF(C3=””,””, TEXT( DATE($A$1,$B$1,C3), “aaa”))”, set up, C3 will automatically display 1 day, while in C2 display the corresponding day of the week (Figure 1).


Figure 1 Set the formula

Tip: IF(DAY(DATE($A$1,$B$1+1,0))<COLUMN(A1),””,COLUMN(A1)): an IF conditional nested function, here first use the DATE function to get the date value, and then the DAY function to display the date. For example, in C3, DATE selects the year and month is January 2019, then display January 1 in C3. Similarly, IF(C3=””,””,TEXT(DATE($A$1,$B$1,C3), “aaa”)) is used to show the corresponding date is the day of the week.

When we choose a different year, month, the value of the first of each month is different, the above function will automatically display different values according to the actual date, you can open the system date to check to see if it is correct (Figure 2).


Figure 2 Display after selecting different years and months

Because the month is only 1 to 12, in order to facilitate us to select different months to quickly generate the task schedule, here you can set the data validity for B1. Select A17, fill in the values from A17 to A28 in order to select the month from 1 to 12. Select B1, click “Data → Data Validation → Data Validation”, in the open window of the validation conditions, select “Serial”, the source select “A17:A28” data source ( Figure 3).


Figure 3 Data validation settings

Now return to the Excel window, the date is set to January 2019, and then select C2, C3, in turn, drag it to the right to fill the formula to fill C2 and C3 to the AG column (i.e., January 31 at), you can see that as the data is filled, the week data above the date will also be filled with the synchronization (Figure 4).


Figure 4 Filling data

Because many companies have Saturday and Sunday as non-working days, in order to facilitate our identification of Saturday and Sunday, here you can also use conditional formatting to fill in the color of Saturday and Sunday data. Select the second row of data, click “Start → Conditional Format → highlight the cell rules → Text Include”, in the window that opens, type “six”, set to select a custom format, the eligible cells will be filled with green display (Figure 5).


Figure 5 conditional formatting settings

Operation as above, and then the cell containing the “day” of the fill for the red text, so that the month contains the date of Saturday and Sunday will automatically fill the above settings for the eye-catching color (Figure 6).


Figure 6 conditional formatting effect

Later when we need to create a monthly work schedule, open the above document as long as the B1 drop-down list to select the month they need, the above date and week data will change simultaneously, we just need to fill in the corresponding text data according to their actual plans. You can see that when we choose February, the data will only be automatically displayed to February 28, so that each month’s work schedule as long as the month needed to choose in B1, and because of the week’s eye-catching tips, work will not be scheduled on Saturdays and Sundays off time (Figure 7).


Figure 7 select the month plan worksheet

The above method is to generate a separate monthly worksheet in the worksheet, if you want to generate a document in a year of 12 months of the work schedule, we can also be achieved by filling the same way. A1:C4 area drop-down fill, then enter “=B1+1” at B5, enter the formula “=IF(C7=””, “”, TEXT(DATE($A$1,$B5,C7), “aaa”))” at C6. that is, the original formula “$B$5” to “$B5”, so that the drop-down formula, the month will automatically be filled, the function to read the month parameters will not be wrong, the original formula is locked B1 display. The same will be changed to C5 formula “= IF (C7 = “”, “”, TEXT (DATE ($A$1, $B5, C7), “aaa”))”, and then select “A5:C8” region down to fill, select C6:C7 to right to fill, so that a full year of the schedule is automatically generated (Figure 8).


Figure 8 Generate annual worksheet

Leave a Comment