Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

conditional formatting date expi

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional Formatting Date Expirations

Conditional Formatting Date Expirations

ResolvedVersion 365

Brandon has attended:
Project Introduction course
Excel Advanced course
Excel Intermediate course

Conditional Formatting Date Expirations

Hello,

I am attempting to use conditional formatting icons (flags) to highlight when inputs in a database are coming close to their expiration date. I understand that the icons can easily be used for percentages and that this formula can work on an individual basis using: =TODAY()+CELL<=90, however I am unsure how to combine the two approaches. In this instance, an example of what I am attempting to achieve is having the dates 12/04/2024, 01/05/2023, 15/12/2022 being formatted green, yellow and red retrospectively (potentially highlight <=90 days, <=365 days and 366+ days remaining). The database has hundreds of rows and therefore would not be feasible to format each one individually with the formula I know. Would anyone be able to assist me with this?

Best regards,
Brandon

RE: Conditional Formatting Date Expirations

Hi Brandon,

Thank you for the forum question.

Please find attached example.

If you select the dates in column A and go to Conditional Formatting -> Manages Rules. If you select my rule and click Edit rule you can see, What I have done.

You will need to Create new rule (you cannot use any of Microsoft's rules).
Under Select Rule Type, select Format all cells based on their values.

Select Icon Sets under format style.

I hope this makes sense.



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

Flag Days.xlsx

Thu 17 Nov 2022: Automatically marked as resolved.

Excel tip:

Calculate difference between two times

For presenting the result in the standard time format (hours : minutes : seconds . Use the subtraction operator (-) to find the difference between times, and the TEXT function to format the returned value to text in a specific number format.

Hours never exceed 24, minutes never exceed 60, and seconds never exceed 60.

=TEXT(B2-A2,"h")
Hours between two times (4)

=TEXT(B2-A2,"h:mm")
Hours and minutes between two times (4:55)

=TEXT(B2-A2,"h:mm:ss")
Hours and seconds between two times (4:55:00)

Where B2 and A2 must hold the end time and start time respectively formatted as a time format

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.