Conditional Formatting formula for middle of month

Drexl88

Board Regular
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

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jasonb75

Well-known Member
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
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
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

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
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

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
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

Drexl88

Board Regular
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

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

Replies
3
Views
117
Replies
5
Views
329
Replies
7
Views
83
Replies
1
Views
67
Replies
7
Views
100

1,128,063
Messages
5,628,379
Members
416,314
Latest member
Dan99321

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.

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

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