How to Conditionally Format Dates Based on Month
To take the calculations and visual presentations a step further, we can use the capabilities of Excel conditional formatting for dates using the Excel MONTH and EOMONTH functions.
Highlight Dates Within the Current Month
If we want to highlight all rows with the current month dates we can follow the following steps:
- Extract the month numbers from dates in a column using the simplest =MONTH(A15) formula.
- Compare these numbers with the current month given by =MONTH(TODAY())
- Based on what the formula returns (TRUE or FALSE), create an Excel conditional formatting rule
Formula:
= MONTH(A15) = MONTH(TODAY())
Example:
Step 1: Select the Data and Create a New Rule with the formula and formatting in Conditional Formatting Manager
Select the Data and Create a New Rule with the formula and formatting in Conditional Formatting Manager
Step 2: Apply the Formula to the selected range
Apply the formula to the selected range
Highlighting Dates by Month and Day
If we want to highlight the gazetted holidays in our worksheet regardless of the year, for e.g. Diwali or New Year, then we can use the DAY and MONTH functions for the same.
Formula:
= AND (OR (DAY(A15) = 25, DAY(A15) = 31), MONTH (A15) = 12)
We use the DAY function to extract the day of the month (1-31) and the MONTH function to get the month number, and then check if the DAY is equal to either 25 or 31 and the MONTH is equal to 12.
Example:
Step 1: Create a New Rule in the Conditional Formatting Manager
Create a New Rule in Conditional Formatting Manager
Step 2: Specify the Formatting that needs to be applied to Cells for which the formula returns TRUE
Specify the Formatting that needs to be applied to Cells for which the formula returns TRUE
Step 3: Select the range for which the formula needs to be applied and click Apply and OK
Select the range for which the formula needs to be applied and click Apply and OK
Using MONTH and EOMONTH functions in Excel
In this easy-to-follow guide, we’ll explore the Excel MONTH and EOMONTH functions. These functions are super handy for working with dates. We’ll show you how to use them to figure out which month a date belongs to, get the first and last day of a month, and more. Whether you’re new to Excel or an experienced user, this tutorial will help you become a pro at handling dates in your spreadsheets. By the end, you’ll be able to breeze through date-related tasks and make your Excel work even smoother. So, let’s dive in and master these functions together!
MONTH and EOMONTH are built-in functions in Excel that assist users in performing date-based operations, particularly those tied to months. Let’s delve into each function and see how they can be useful: