Conditional Formatting of Approching Date Based on Date in Another Cell

[jwest510]

My team was so impressed with the results of my spreadsheet (thank you very much to forum members Tetra201, jtakw and etaf) they asked for a little more. I've seen many posts regarding formatting dates based using the TODAY formula but it's not quite working for me.

This question is for a spreadsheet that tracks the record retention dates of various documents. I would like to have the destruction date change font color when it is within 30 days of its destruction date.

So for example:

• In F5 there is a manually entered date of when the record was created.
• In G5 there is a formula that produces an value (such as 3 or 7), based on input from B5, for the years a record must be retained.
• In H5 there is the formula =EDATE(F5,G5*12)

Would someone help me with a Conditional Formatting formula that will allow me to change the date's font in H5 to red when it is within 30 days of H5's date?

 

[Joe4]

The key is to realize that Excel tracks dates as numbers, specifically the number of days since 1/0/1900. So to get the number of days between two valid dates, all you need to do is subtract them.
So, if you want to highlight cell H5 based on whether it is 30 days or less from the current date, you would use a Conditional Formatting formula like this:

=(H5-TODAY()) <= 30

 

[jwest510]

Look at me! Learning to fish. Thank you very much Joe4!

 

[Joe4]

You are welcome. Glad I could help!