Source
r/excel•
totalrandomguy

Conditional formatting to highlight cells that dates are in the past (RED), Coming in the next 31 days (Orange), Any date after 31+days (yellow). In excel 2011 on mac.

solved

I am trying to make a semi automatic spread sheet for my work so that I can automatically track safety certificate expiry dates.

I need dates that have expired to highlight red, expiring in the next 31 days orange and any dates after 31 days yellow.

There are lots of examples online, but they seem to not be compatible with the version of excel I am using (2011 on Mac) or dont cover formula for all of my conditions.

Any help is greatly appreciated.

Posted Jan 30, 2018, 11:02 AM UTC

38
ForgotHowToAirForce1•

I have used a helper column to calculate the difference between dates. In an unused cell, place a today formula =today()-remember to lock this cell with F4. Then in your helper column, subtract the cell with the expiration date from the today cell. Use this number of days for your conditional formatting.

3
totalrandomguyOP•

SOLUTION VERIFIED

2
Clippy_Office_AsstMOD•

You have awarded 1 point to ForgotHowToAirForce

1
totalrandomguyOP•

Thank you for the quick response.

I am sorry I am very new to Excel and Im struggling to understand. I get the bit of making a cell with todays date I have set that up now and locked it.

But what specifically do I type/where to set up this date to conditional format my expiry dates?

1
ForgotHowToAirForce1•

Once you create your helper column and have it calculate the difference between dates, you can apply conditional formatting on any cell(s) you want, using that number for the condition to trigger the formatting. You would need three conditions, one for each criteria.

2
totalrandomguyOP•

Thank you very much I re read your comment and realised i was being silly and you was very clear I have it all working now thank you very much.

1
_mueller4•

Utilize conditional formatting. An example:

  1. Place a list of dates in range A1 through A10.

  2. Select range A1 through A10.

  3. With the range selected, navigate to 'Home' > 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'

  4. In the formula bar within the popup box, type:

    =A1>TODAY()

When this condition is true, the rule will be triggered.

  1. Click 'Format' and set the desired formatting options for when the rule is triggered.

  2. Click 'OK'

  3. If the rule does not appear to be applied to your selected range, navigate to 'Home' > 'Conditional Formatting' > 'Manage Rules' and ensure that the rule you created is applicable to the desired range.

As a side note, I am using Excel 2013 on Windows, but I do not think that the version will create an issue for you here. If you found this answer helpful, please respond with 'Solution Verified'.

2
totalrandomguyOP•

thank you very much, i will try this now

1