Help with IF THEN type of function

[Alan R]

Would appreciate some help with the following -
We keep an Excel spreadsheet to track our suppliers insurance documents but need some help in setting up the Expired field.

The Column Heading is Expiry Date and in this field we capture the expiry date of the insurance certificate. I would like to automate this field by using a formula to look at the date in the field, comparing it to the current date and firstly to change the text to red 1 month before the expiry date and then to change the text in the field to "Expired" 1 day after the noted expiry date.

Is this something that can be done?
thank you
Alan

[AliGW]

Yes, but it will be easier to advise precisely with a sample workbook.

Read the yellow banner at the top of the page.

[Alan R]

Sample of proposed register attached.
Reflected expiry date is 20 March 2022, on 20 February 2022 would want the date 20 March 2022 to change to red text to flag the requirement to obtain renewed certificate. On 21 March 2022, would want the date to be
be replaced with the word "Expired".

[tanasedn]

Sample of proposed register attached.
On 21 March 2022, would want the date to be
be replaced with the word "Expired".

It cannot be done in the same cell. You need to add another column to capture the text "Expired".

[Glenn Kennedy]

Yes... 2 possible alternatives. You could:

a) have the date amended with a strikethrough once expired

b) render the expired date invisible by either making the font white, or the cell fill colour black.

[tanasedn]

I have a solution but it seems I was blocked.
In conditional formatting for month request use the

Please Login or Register  to view this content.

and choose red for font color.
for "Expired" the formula is

Please Login or Register  to view this content.

and in format window, for number choose CUSTOM and type double quoted "Expired". Choose another color for fill

[Glenn Kennedy]

Blocked? What do you mean by that?

[tanasedn]

Blocked? What do you mean by that?

When I tried to post I was redirected to sucuri.net site. But now it work.

[Alan R]

I have a solution but it seems I was blocked.
In conditional formatting for month request use the

Please Login or Register  to view this content.

and choose red for font color.
for "Expired" the formula is

Please Login or Register  to view this content.

and in format window, for number choose CUSTOM and type double quoted "Expired". Choose another color for fill

Thank you for the assistance.

I understand the Conditional Formatting. However I don't understand how column F you have inserted with a formula only in F4 relates to the Conditional Formatting.
I am trying to understand the formatting so I can recreate for Column H. I have tried to copy the process but I am not getting the same result.

[AliGW]

You can delete that column F - it's not needed. There are two CF rules that relate to column E.