Teach you five ways to determine the day of the week in Excel

This article brings you relevant knowledge about excel, which mainly introduces how to determine the day of the week today, including methods such as custom formats, text functions, WEEKDAY functions, etc. , let’s take a look at it, I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

How to determine what day of the week a date is? There are many ways to solve this problem:

Method 1: Custom format

If you need to display the date and week in the cell at the same time, you can select the area where the date is located and press the Ctrl 1 key combination. Set custom number format:

yyyy/m/d aaaa

Method 2: TEXT function

if needed in another line To display only the day of the week, you can use the following formula:

=TEXT(B1,"aaa")

The function of the TEXT function is to change the number into the specified Formatted text, in this case the format code uses "aaa", the result is that only the day of the week is displayed.

Method 3: WEEKDAY function

Using the WEEKDAY function, you can return the day of the week for a certain date in the form of a number.

=WEEKDAY(B1,2)

The second parameter uses 2, which means that the numbers 1~7 represent Monday to Sunday.

Method 4: Conditional formatting

Combine the WEEKDAY function with conditional formatting to highlight weekend dates.

Select the cell range A2:A9, create a new conditional formatting rule, use the formula:

=WEEKDAY(A2,2)>5

Finally click [Format] 】 button to set the fill color.

Method 5: MOD function

If you want to determine whether a certain date is a working day or a weekend, you can also use the MOD function to process it.

=IF(MOD(A2,7)

First use the MOD function to calculate the date and The remainder when divided by 7 is exactly 0 and 1 when dividing the serial value of a weekend date by 7.

Then use the IF function to judge. If the result of the MOD function is less than 2, it is the weekend, otherwise it is the working day.

Related learning recommendations: excel tutorial

The above is the detailed content of Teach you five ways to determine the day of the week in Excel. For more information, please follow other related articles on the PHP Chinese website!