[Excel] How to create a calendar with Saturdays, Sundays, and holidays colored in!
Table of Contents
0:04 Creating a Calendar
0:57 Coloring Saturdays and Sundays
1:50 Coloring Holidays
Click here for instructions:
[How to Create an Automatically Changing Calendar]
1. Select the cell where you want to enter the first day.
2. Enter "=DATE(year cell, month cell, 1)"
3. Move to the adjacent cell and enter "=date cell + 1"
4. Autofill to the right
5. Enter "=TEXT(date cell, "aaa")" in the day of the week cell.
6. Autofill to the right
[How to Color Saturdays and Sundays]
1. Select the range you want to color in the calendar.
2. Conditional Formatting → New Rule → Use a formula to determine the cells to format.
3. =WEEKDAY(day cell) = Day of the Week
To reference a cell, press F4 to reference the row. Example: D$3
The number representing the day of the week is 7 for Saturday and 1 for Sunday.
④ Select a fill color from the formatting menu.
⑤ Press OK.
[How to Color Holidays]
① Press Shift and F11 to create a new sheet.
② Copy and paste the list of holidays into the new sheet.
③ Select the range you want to color.
④ "Conditional Formatting" → "New Rule" → "Use a formula to determine which cells to format."
⑤ "=COUNTIF(Holiday Date, First Date) = 1."
⑥ Select a fill color from the formatting menu and press OK.
#Excel
#OfficeWork
#ComputerSkills
#WorkEfficiency