How to quickly distinguish the dates in Excel, which weekdays and which weekends are?
Recollection 2020-11-25 16:02:57
If there are many dates in the data table , How to quickly know which days are weekends ?

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 .

 How to quickly distinguish Excel Date in , Which weekdays , Which are weekends ?


 How to quickly distinguish Excel Date in , Which weekdays , Which are weekends ?


Solution :



1. Select the area where you want to set rules , namely A2:C25 --> Choose... From the menu bar “ Start ”-->“ Conditional format ”-->“ New rules ”

 How to quickly distinguish Excel Date in , Which weekdays , Which are weekends ?


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($A2,2)>5

 How to quickly distinguish Excel Date in , Which weekdays , Which are weekends ?


weekday Function description



effect :
  • Returns the day of the week corresponding to a date ;
  • By default , Days are 1( Sunday ) To 7( Saturday ) Range of integers .


grammar :
  • WEEKDAY(serial_number,[return_type])


Parameters :
  • 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 .

 How to quickly distinguish Excel Date in , Which weekdays , Which are weekends ?


explain :
  • 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 :
  • WEEKDAY($A2,2):
    • 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 ”

 How to quickly distinguish Excel Date in , Which weekdays , Which are weekends ?


4. Click on “ determine ”

 How to quickly distinguish Excel Date in , Which weekdays , Which are weekends ?


All lines with weekend dates are automatically filled with green .

 How to quickly distinguish Excel Date in , Which weekdays , Which are weekends ?


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 .
版权声明
本文为[Recollection]所创,转载请带上原文链接,感谢
https://fheadline.com/2020/11/20201125160027503h.html
相似文章

2021-08-09