Case study ：
Employees of a company need to check the branches from time to time , The figure below 1 For the schedule .
The time of spot check includes working days , There are also weekends . If the date is a weekend , Please automatically highlight the entire line .
The effect is as follows 2 Shown .
1. Select the area where you want to set rules , namely A2:C25 --> Choose... From the menu bar “ Start ”-->“ Conditional format ”-->“ New rules ”
2. Select in the pop-up dialog box “ Use the formula to determine which cells to format ”--> Enter the following formula --> Click on “ Format ”：
weekday Function description
- Returns the day of the week corresponding to a date ;
- By default , Days are 1（ Sunday ） To 7（ Saturday ） Range of integers .
- serial_number： It's necessary , The date you need to find , The parameter cannot be in text format ;
- return_type： Optional , The number used to determine the type of return value .
- Excel The date can be stored as a sequence number that can be used for calculation . By default ,1900 year 1 month 1 The serial number of the day is 1, and 2008 year 1 month 1 The serial number of the day is 39448, It's because it's away from 1900 year 1 month 1 The day has 39448 God ;
- If the base value of the current date is serial_number Out of range , The error value is returned #NUM！;
- If return_type Beyond the range specified in the above table , The error value is returned #NUM！.
Definition of formula ：
- Calculate the days of the week in a range of cells ;
- Return from Monday to Sunday 1 to 7;
- WEEKDAY($A2,2)>5： If the return value is greater than 5, It's the weekend , Meet the trigger condition .
* Parameters $A2 The column value of requires an absolute reference to , And row values need relative references .
3. Select in the pop-up dialog box “ fill ” tab --> Select the desired fill color --> Click on “ determine ”
4. Click on “ determine ”
All lines with weekend dates are automatically filled with green .
Many students will feel that Excel Individual case studies are fragmented , Beginners may not be able to fully understand and master . Many students hope to have a complete set of graphic teaching , Start with the most basic concepts , Step by step, from simple to complex 、 From entry to mastery , Explain systematically Excel Various knowledge points .
Now there's finally , The following column , Starting with the most basic operations and concepts , Vivid 、 Interesting cases lead you to master one by one Excel Operation skills of 、 Shortcut key 、 Function formula 、 PivotTable 、 Chart 、 Printing skills, etc …… Finish the whole book , You can be Excel master .