Conditional Formatting

Resolved · High Priority · Version 365

Wed 20 Apr 2022, 16:06 Delegate Neal said...

Neal has attended:
Excel Intermediate course

Conditional Formatting

Hi,

I have a spreadsheet where we record expiry dates of training certificates for our employees. I want to be able to conditional format these cells so that they can change colour to show when the certificate is valid, soon to expire and has expired.

My problem is that the H&S certificates are valid for 2 years and more so I cannot seem to find the right formula or rule to take the current date and the expiry date of each certificate to show its status. I want to be able to have the cells change with the current date.

For example, a First Aid certificate is valid for 3 years. How would I use Excel to show me in the RAG system if a First Aid certificate is still valid or not?

Thu 21 Apr 2022, 10:03 Trainer Marius said...

RE: Conditional Formatting

Hello Neal,

Thank you for your question. It might be a good idea to have the different types of certificates' expiry dates in different columns if they are not already.

You need to create 3 rules for each column of dates. One rule for expired certificates, one for certificates about to expire and another for valid certificates.

You could create these rules as follows:

1. Expired certificates (<=36 months):
Click Conditional Formatting - New Rule - Use a formula to determine which cells to format
In the box below "Format values where this formula is true", type the following formula:

=$A2<=EDATE(TODAY(),-36)

where A2 holds the first date.

Remember to set a formatting colour before clicking OK.

This formula works for certificates which expire after 3 years (36 months).

2. Certificates expiring in 1 month or less: (<=35 months)
Click Conditional Formatting - New Rule - Use a formula to determine which cells to format
In the box below "Format values where this formula is true", type the following formula:

=$A2<=EDATE(TODAY(),-35)

Remember to set a formatting colour before clicking OK.

3. Valid certificates (>35 months):
Click Conditional Formatting - New Rule - Use a formula to determine which cells to format
In the box below "Format values where this formula is true", type the following formula:

=$A2>EDATE(TODAY(),-35)

Remember to set a formatting colour before clicking OK.

The above should be the order of your rules in the Conditional Formatting box.

The EDATE function requires you to think in multiples of 12 (12 months in a year), so for 2 year expiry, it would be 24 months.

I hope this helps.

Kind regards
Marius Barnard
STL

Thu 21 Apr 2022, 10:26 Trainer Jens said...

RE: Conditional Formatting

Hi Neal,

Thank you for the forum question.

I have used a combination of the Datedif, And, & the Today function to achieve what you want.

Please find attached Excel file with the solution.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

certificates Con Fomat.xlsx

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Manually rotating a 3D chart

To manually rotate a 3D chart;
Select the chart (edit mode) and click on one corner of the chart display area. A selection border will appear, using the left mouse button on one of the chart's "corners" (mouse pointer changes to a cross) drag the image which becomes a line representation of the chart (as you begin to drag) and rotate the chart to a view of your choice.
If you dont get it right first time, dont worry, the manoeuver is a little bit tricky.

View all Excel hints and tips