Conditional formatting

[Daahak]

Hi All,

Id no	Name	Passport type	Expiration day
1	John	Diplomatic	18 march 2023
2	Marie	Turist	05 december 2022
3	Michael	Blue	27 mai 2020
4	Nick	Old	12 january 2023
5	Matew	(Blank)	(Blank)

I am trying to create a conditional formating rule for a working spreadsheet I use at work.
As you see in the table i have names, passport types and the expiration date for each (all the data in the table are fictive). So what I need is a formating that will change the color of the cells in the expiration date but with some conditions…
For every different type of passport I have a different period of time before the date occur when i have to submit the paperwork for revalidation
For diplo, turist and blue passports before expiration with 180 days
For old passports before expiration with 10 days

So… i want to:

• fill the expiration day cell with red if is a diplo passport and there are less then 180 days untill expires,
• fill the expiration day cell with red if is an old passport and there are less then 10 days untill expires
• do nothing for the cells that are blank untill i write someting in (a guy gets a new passport)

Can someone assist me in this?

 

[Joe4]

Welcome to the Board!

Assuming your data starts in cell A1, then select range D2:D6 and use this Conditional Formatting formula:

=OR(AND(OR($C2="Diplomatic",$C2="Turist",$C2="Blue"),$D2-TODAY()<180),AND($C2="Old",$D2-TODAY()<10))

and choose your red highlighting color.

 

[Daahak]

Welcome to the Board!

Assuming your data starts in cell A1, then select range D2:D6 and use this Conditional Formatting formula:

=OR(AND(OR($C2="Diplomatic",$C2="Turist",$C2="Blue"),$D2-TODAY()<180),AND($C2="Old",$D2-TODAY()<10))

and choose your red highlighting color.

Worked perfect! Tks so much!

 

[Joe4]

You are welcome.
Glad I was able to help!