Conditional formatting to identify a date in x month's time (3 months and 6 months)

Hello clever people. I have a workbook that has dates along the top as row headers. the dates are 01/MM/YY but formatted to show mmm-yy. I need to be able to quickly show month's that are in...

excel

Formulas and Functions

office 365

HansVogelaar
Feb 27, 2023
SuziDurham

Select the dates in the header row.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'equal to' from the second drop down.

In the box next to it, enter the formula

=EOMONTH(TODAY(), 2)+1

Click the Format... button.

Activate the Fill tab.

Select a color.

Click OK, then click OK again.

Repeat these steps, but with the formula

=EOMONTH(TODAY(), 5)+1

Select the same fill color or a different one, according to your preference.

laurablow
Copper Contributor
Apr 09, 2024
Hi HansVogelaar

I wonder if you can help with my conditional formatting issues, once again.

Similar to above, I'm looking to highlight cells if the dates comes within 2 months of todays date, but the standard date formula will only allow up to 1 month. Is there a simple formula that I can use for this?

Also, I have an issue with a SUMPRODUCT. I was attempting to make COUNTIFS work in a way that will count how many cells from D2:P2 have dates in the past, but only if the date in C2 is in the future.
I was given the following formula to help: =SUMPRODUCT((C2>=TODAY())*(D2:P2<=TODAY())*(D2:P2<>""))

But I have a further requirement for the above formula. C2 will either contain "N/A", a date in the future or a date in the past. I'd like it to only count up for the cells that have dates in the future, not dates in the past, or N/A.

I appreciate if this isn't your remit but thanks in advance for any help you can provide!
- HansVogelaar
  MVP
  Apr 09, 2024
  laurablow
  
  For the conditional formatting rule:
  
  Select the range with the dates.
  On the Home tab of the ribbon, click Conditional Formatting > New Rule...
  Select 'Format only cells that contain'.
  Leave the first drop down set to 'Cell Value'.
  Select 'less than or equal to' from the second drop down.
  In the box next to it, enter the formula
  
  =EDATE(TODAY(), 2)
  
  Click Format...
  Activate the Fill tab.
  Select a highlight color.
  Click OK, then click OK again.
  
  For the formula: is the N/A a text value or an error value?
  - laurablow
    Copper Contributor
    Apr 09, 2024
    Hi,
    
    Thanks very much - for the formula, the N/A is a text value.
SuziDurham
Brass Contributor
Feb 27, 2023
Hans! Thank you! I have searched so many different resources for this and you've solved it so easily!

I have followed your instructions and it works. Thank you! Thank you! Thank you! 😄

Forum Discussion

Conditional formatting to identify a date in x month's time (3 months and 6 months)

Resources