Excel addicted
New Member
- Joined
- Sep 17, 2019
- Messages
- 14
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
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