# Conditionnal formating_Change color when the date exceeds a defined period

##### New Member
Hi Excel Community!
Hope you are doing well.
I'm trying to get it right in conditionnal formating but it seems I can't find the solution.
I have 4 quarters per years
Q1: from 01/01/2019 - to 31/03/2019
.............
Q4 : from 01/10/2019 to 31/12/2019
And I havea list of payment dates. When the payment date is due by one year from the reporting quarter , the row font should turn red. I would like a dynamic formula.
I tried the following formula : DATE(YEAR(VALUE(RIGHT(\$A\$6;10)));MONTH(VALUE(RIGHT(\$A\$6;10)));DAY(VALUE(RIGHT(\$A\$6;10))))-\$K12-1>=366
But it seems that the second row in my table ( see the attached picture) turns wrongly red. The date 15/10/2019 in cell F13, is within the one year time limit of the reporting quarter in cell A6. So it shouldn't be red.

#### Attachments

• Question.PNG
27.7 KB · Views: 6

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### etaf

##### Well-known Member
so
DATE(YEAR(VALUE(RIGHT(\$A\$6;10)));MONTH(VALUE(RIGHT(\$A\$6;10)));DAY(VALUE(RIGHT(\$A\$6;10))))
Extracts the date from your period - have you tested that is OK
Also
DATE(YEAR(VALUE(RIGHT(\$A\$6;10)))-1;MONTH(VALUE(RIGHT(\$A\$6;10)));DAY(VALUE(RIGHT(\$A\$6;10))))
Takes that period back by 1 year

Whats in K12?

Payment date looks like F12

Have you thought of
DATE(YEAR(VALUE(RIGHT(\$A\$6;10)))-1;MONTH(VALUE(RIGHT(\$A\$6;10)));DAY(VALUE(RIGHT(\$A\$6;10))))
Which takes 1 year away from the reporting period
and is that greater than F12

=\$F12 < DATE(YEAR(VALUE(RIGHT(\$A\$6;10)))-1;MONTH(VALUE(RIGHT(\$A\$6;10)));DAY(VALUE(RIGHT(\$A\$6;10))))

#### DRSteele

##### Well-known Member
I think this might be easier.

MrExcel posts18.xlsx
ABCD
2Period from 10/01/2020 to 12/31/2020
3
4Q4 Begin10/1/2020
5Q4 End12/31/2020
6
7InvoiceAmountPayment DateWith-out One year of Quarter?
8InvoiceA\$3,0009/16/2019TRUE
9InvoiceB\$9,00010/16/2019FALSE
10InvoiceC\$25,00010/22/2020FALSE
Sheet16
Cell Formulas
RangeFormula
D2D2="Period from "&TEXT(B4,"MM/DD/YyyY")&" to "&TEXT(B5,"MM/DD/yyYY")
D8:D10D8=NOT((\$C8>=(EOMONTH(\$B\$4,-13)+1))*(\$C8<=\$B\$5))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A8:D10Expression=NOT((\$C8>=(EOMONTH(\$B\$4,-13)+1))*(\$C8<=\$B\$5))textNO

##### New Member
so
DATE(YEAR(VALUE(RIGHT(\$A\$6;10)));MONTH(VALUE(RIGHT(\$A\$6;10)));DAY(VALUE(RIGHT(\$A\$6;10))))
Extracts the date from your period - have you tested that is OK
Also
DATE(YEAR(VALUE(RIGHT(\$A\$6;10)))-1;MONTH(VALUE(RIGHT(\$A\$6;10)));DAY(VALUE(RIGHT(\$A\$6;10))))
Takes that period back by 1 year

Whats in K12?

Payment date looks like F12

Have you thought of
DATE(YEAR(VALUE(RIGHT(\$A\$6;10)))-1;MONTH(VALUE(RIGHT(\$A\$6;10)));DAY(VALUE(RIGHT(\$A\$6;10))))
Which takes 1 year away from the reporting period
and is that greater than F12

=\$F12 < DATE(YEAR(VALUE(RIGHT(\$A\$6;10)))-1;MONTH(VALUE(RIGHT(\$A\$6;10)));DAY(VALUE(RIGHT(\$A\$6;10))))
Hello etaf,
Actualy it's F12 not K12. I copied and paste the formula before making the change for cell reference of the payment date .
I tried the formula but it guess the DrSteel post works perfectly for me.

##### New Member
I think this might be easier.

MrExcel posts18.xlsx
ABCD
2Period from 10/01/2020 to 12/31/2020
3
4Q4 Begin10/1/2020
5Q4 End12/31/2020
6
7InvoiceAmountPayment DateWith-out One year of Quarter?
8InvoiceA\$3,0009/16/2019TRUE
9InvoiceB\$9,00010/16/2019FALSE
10InvoiceC\$25,00010/22/2020FALSE
Sheet16
Cell Formulas
RangeFormula
D2D2="Period from "&TEXT(B4,"MM/DD/YyyY")&" to "&TEXT(B5,"MM/DD/yyYY")
D8:D10D8=NOT((\$C8>=(EOMONTH(\$B\$4,-13)+1))*(\$C8<=\$B\$5))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A8:D10Expression=NOT((\$C8>=(EOMONTH(\$B\$4,-13)+1))*(\$C8<=\$B\$5))textNO
Thanks a lot DrSteele ! Your formula anwsers my question. It's clear and neat.
I spent some time figuring out how it works and now I get it !
I'm very grateful

#### DRSteele

##### Well-known Member
You're welcome. I'm glad to have been able to help. Best of luck with your project.

Replies
19
Views
609
Replies
8
Views
289
Replies
4
Views
100
Replies
1
Views
126
Replies
3
Views
201

1,130,082
Messages
5,639,967
Members
417,120
Latest member
Pavithra devi

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

### Which adblocker are you using?

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