30 Nov, 2022

[Excel] How to color-code Saturdays and Sundays in Excel

Find Article

Impress Watch Series Popular Articles

1 hour 24 hours 1 week 1 month

How to use Excel

I want to create a color-coded schedule for Saturdays and Sundays!

Let's create a schedule with color-coded Saturdays and Sundays in Excel

　After the middle of December, the atmosphere of the end of the year will increase. Many of us are probably starting to think about our plans for the New Year. Why don't you create a schedule using Excel to properly manage work-related meetings, events, and private schedules?

In the previous article, I explained how to add the day of the week to the end of the date in Excel. This time, I will take it a step further and explain how to create a schedule that says "Saturday is blue, Sunday is red."

Creating a ``Blue on Saturday and Red on Sunday''

Use the following ``January 2022 schedule'' to become ``Blue on Saturday and Red on Sunday'' Create a schedule like This time, we will use conditional formatting and the TEXT function to set the text color of the date to blue or red depending on the day of the week.

In addition, enter the date data in the "Date" column of column A (①). Here, as explained in the previous article, the value entered as "2022/1/1" is displayed as "2022/1/1 (Saturday)" by setting a user-defined display format. You can leave it as "2022/1/1".

Enter the date data in the "Date" column of column A (①)

First, let's set the text color for Sunday's date to red. Select the cell range "A3:A33" (②) where the date is entered. Click [Home] tab (3) → [Conditional formatting] (4) → [New rule] (5).

Select the cell range "A3:A33" (②) where the date is entered. Click the [Home] tab (3) → [Conditional formatting] (4) → [New rule] (5)

The [New formatting rule] dialog box will be displayed. Please] column, select [Use a formula to determine which cells to format] (⑥). Below that, in the [Format value when the following formula is met] field, use the TEXT function and enter "=TEXT(A3,"aaa")="day"" (⑦).

The TEXT function is a function that converts the numeric value of the argument to a character string and displays it in the specified display format. By entering "=TEXT(A3,"aaa")", you can display the day of the week corresponding to the date entered in cell A3 as an abbreviation such as "day" or "month". If you enter "=TEXT(A3,"aaa")="day"", it will be a conditional expression that "the day of the week entered in cell A3 is Sunday".

Select [Use a formula to determine which cells to format] (6) from the [Select a rule type] field. Below that, in the Format value if the following formula is true field, use the TEXT function and enter "=TEXT(A3,"aaa")="day"" (⑦)

[Excel] Saturday and Sunday dates in Excel

After entering the conditional expression, click [Format] (⑧) and set the format to be applied when this conditional expression is met. This time, set the text color to red.

The [Format Cells] dialog box will be displayed. From the [Color] field on the [Font] tab (⑨), select the color (⑩) that you want to set as the text color.

Next, click [Format] (⑧) and select the color (⑩) that you want to set as the text color from the [Color] column on the [Font] tab (⑨)

　After completing the format settings, Click [OK] (11).

The [New Format Rule] dialog box will be displayed again. Confirm that the set format is displayed in [Preview] (12) and click [OK] (13).

Confirm that the set format is displayed in [Preview] (12) and click [OK] (13)

When you return to the sheet, the text color of Sunday's date has been set to red ( ⑭).

When you return to the sheet, the text color for Sunday's date has been set to red (⑭)

Similarly, let's set the text color for Saturday's date to blue. In this case, enter "=TEXT(A3,"aaa")="Sat"" (⑮) in the "Format value if the following formula is satisfied" field in the "New Formatting Rule" dialog box. .

In the [New Formatting Rule] dialog box, enter "=TEXT(A3,"aaa")="Sat"" (⑮) in the [Format value if it satisfies the following formula] field

, the cells with dates on Saturday are blue, and the cells with dates on Sunday are red (⑯).

It was possible to set the date cells for Saturdays to be blue and the cells for Sundays to be red (⑯)

If you also want to format cells for dates on holidays, refer to the previous article. please.

Let's create a schedule with ``blue for Saturdays and red for Sundays''

This time, we explained how to create a schedule with color-coded Saturdays and Sundays in Excel. The conditional formatting feature allows you to efficiently create a "blue for Saturday, red for Sunday" schedule. Everyone, please try it once.

Prev Next