Conditionnal formating_Change color when the date exceeds a defined period

Excel addicted

New Member
Joined
Sep 17, 2019
Messages
10
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.

Please help on this !
Thanks in advance
 

Attachments

  • Question.PNG
    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
Joined
Oct 24, 2012
Messages
4,680
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Mar 31, 2015
Messages
2,382
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel addicted

New Member
Joined
Sep 17, 2019
Messages
10
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.
I appreciate your help ;)
 

Excel addicted

New Member
Joined
Sep 17, 2019
Messages
10
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 :biggrin: !
I'm very grateful
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,382
Office Version
  1. 365
Platform
  1. Windows
You're welcome. I'm glad to have been able to help. Best of luck with your project.
 

Watch MrExcel Video

Forum statistics

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