HomeMicrosoft OfficeExcelHow to Check for Weekend in Microsoft Excel ?

How to Check for Weekend in Microsoft Excel ?

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.
Check for Weekend in Microsoft Excel
  • To find the weekend among these dates, do the following:
  • Click on the cell C2 and enter the formula  =WEEKDAY(A2,2)
Check Weekend in Microsoft Excel
  • 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.
Check for Weekend in Microsoft Excel
How to Check for Weekend in Microsoft Excel ?

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
Check for Weekend in Microsoft Excel
  • 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.
Check for Weekend in Microsoft Excel
  • Select format and a dialog box appear.
  • Select Fill and choose a color to highlight the weekend days.
Check for Weekend in Microsoft Excel
  • Click Ok.
Check for Weekend in Microsoft Excel

Now the cells with weekend days are highlighted.

Leave a Reply

You May Also Like

In this article, you will learn about the SKEW.P function, the formula syntax and usage of the function in Microsoft...
In this article, you will learn about the SKEW function, the formula syntax and usage of the function in Microsoft...
In this article, you will learn about the RANK.EQ function, the formula syntax and usage of the function in Microsoft...
  • Excel
  • November 23, 2021