Conditional Formatting formula for middle of month

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
66
Office Version
  1. 365
Platform
  1. Windows
I am using the below formula to highlight cells red when the last day of the month has passed - 2 months after the date occurred. I now want cells to go amber at the mid point of the month - 2 months after the date occurred and green when the date is earlier. any help would be greatly appreciated.

=TODAY()>EOMONTH(G5,2) - red
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
2 calendar months or 2 whole months (~60 days)?

e.g. should December dates after the 15th turn amber in mid February or mid March?
 

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
66
Office Version
  1. 365
Platform
  1. Windows
2 calendar months or 2 whole months (~60 days)?

e.g. should December dates after the 15th turn amber in mid February or mid March?
sorry, 2 calendar months. so any date in December amber in mid Feb.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
In that case, try
Excel Formula:
=TODAY()>(EOMONTH(G5,1)+14)
which will turn TRUE on the 15th.

Make sure that the rule for the red highlight is at the top of the list and that the 'Stop if True' box is checked.
 

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
66
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks. That worked.

Using the same criteria what would the formula look like to count all of the compliant dates in the column. So S4:S79 contains the highlighted dates, how could I count how many of those dates fall inside of the criteria (not red) so how many cells have a date that the end of the second month after it has not passed.

Thanks
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
Maybe something like
Excel Formula:
=SUMPRODUCT(--(S4:S79<=EOMONTH(G4:G79,2)))
This is assuming that S4 is being compared to G4, S5 to G5, etc.
 

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
66
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Maybe something like
Excel Formula:
=SUMPRODUCT(--(S4:S79<=EOMONTH(G4:G79,2)))
This is assuming that S4 is being compared to G4, S5 to G5, etc.
Apologies, I wasn't clear. The conditional format rule applies to multiple rows/columns (G:R). Column S uses max function to bring the most recent date from each row to the end of the data. So I am trying to count how many dates in column S are compliant with the same rule set in the CF. For example, the date in S5 is earlier than the end of the second month from said date - at the bottom i am trying to calculate total number of dates in column S that meet the criteria. I can upload the data if still not clear.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
A visual example would be helpful, it looks like it should be something fairly simple but I seem to have the concentration of a goldfish with dementia today :oops:
 

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
66
Office Version
  1. 365
Platform
  1. Windows
A visual example would be helpful, it looks like it should be something fairly simple but I seem to have the concentration of a goldfish with dementia today :oops:

Hi Jason, I managed to resolve it using what you had already provided. Many thanks for you help :)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,717
Members
415,853
Latest member
Newlife72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top