If you want to use conditional formatting to highlight dates that are in the same month and year as another date, you can use a simple formula based on the TEXT function.
For example, if you have dates in the range B4:G11, and want to highlight dates that have the same month and year as the date June 1, 2015, select the range B4:G11 and create a new CF rule that uses this formula:
Note: it's important that CF formulas be entered relative to the "active cell" in the selection, which is assumed to be B4 in this case.
Once you save the rule, you'll see all dates that occur in June 2015 highlighted.
How this formula works
This formula uses the TEXT function to concatenate the month and year of each date. Then, the two dates are tested for equality. TEXT is a useful function that allows you to convert a number to text in the text format of your choice. In this case the format is the custom date format "myyyy", which translates to: month number without leading zeros & 4-digit year. For example, if A1 contains the date 9-Jun-2015, TEXT(A1,"myyyy") will produce the text string "62016".
Use other cells for input
You don't need to hard-code a date into the rule. To make a more flexible rule, you can use another cells like a variable. For example, if you name cell E2 "date", you can rewrite the formula like so:
If you want to highlight dates greater than or less than a certain date with conditional formatting, you can use a simple formula that relies on the date function. For example, if you have dates in the cells B4:G11, and want to highlight cells that...
If you want to highlight dates between two dates with conditional formatting, you can use a simple formula that uses the AND and DATE functions together. For example, if you have dates in the range B4:G11, and want to highlight cells that contain a...
To count dates in a given year, you can use the SUMPRODUCT and YEAR functions. In the example shown, the formula in E5 is: = SUMPRODUCT ( -- ( YEAR ( dates ) = D5 )) where "dates" the a named range B5:B15. How this formula works The YEAR function...
The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.