This post explains the weekday formula in Microsoft Excel and how you can use it to find and highlight the weekends easily.
How to Check for Weekend in Microsoft Excel ?
Microsoft Excel provides a feature to check for the day and also analyze if it is a weekday or weekend. In Microsoft Excel, we can easily find and highlight the weekends by simply using a formula and conditional formatting.
Let’s look into an example of how to highlight cells that have weekend dates.
- Consider the data with a range of cells with dates.
- To find the weekend among these dates, do the following:
- Click on the cell C2 and enter the formula =WEEKDAY(A2,2)
- Here in this weekday formula, A2 is the cell value where the date is being entered and then displays a list of options showing numbers corresponding with days. You can choose one by entering the option number.
- Here the option is chosen as 2. i.e 1 for Monday, 2 for Tuesday,……6 for Saturday,7 for Sunday
- Click Enter. And now you can see numbers corresponding to days being entered automatically.
- Now use the fill handle and copy it to all the cells down by drag and drop.
You can find the numbers 6 and 7 indicating Saturdays and Sundays respectively.
After finding these you can highlight the cells with Saturday and Sunday by doing the following
- Copy the weekday formula that you have entered in C2 for future use.
- Select the entire column of dates.
- Select Home tab -> Conditional Formatting -> New Rule
- A dialog box appears, select Use a formula to determine which cells to format.
- Then paste the formula in the below box and alter it to =WEEKDAY(A2,2) >5 as the values of Saturday and Sunday are 6 and 7.
- Select format and a dialog box appear.
- Select Fill and choose a color to highlight the weekend days.
- Click Ok.
Now the cells with weekend days are highlighted.
