Need to calculate when a date is = or less than 30 days prior to today's date

I manage truck drivers and I need a way to be notified when their license and/or medical cards are within 30 days of expiration. For example, if a drivers license expires May 7, 2016 - I need the cell to turn red today (April 7, 2016). I have the formula =E3-30 to let me know what day is 30 days prior to expiration. But I need that cell to turn red when it's 30 days from any given "today". Hope I explained this okay...Thank you for any help.

Hi,

Use a conditional format. WIth the expiry date in A1 then set the A1 CF to

Formula:

Please Login or Register  to view this content.

http://www.excelforum.com/excel-prog...days-date.html

Co-incidence or what?

Easy peasy.

Name a cell such as THE_DAY, and use the named cell range in your conditional formatting formula rather than TODAY().
Then you can insert the formula =TODAY() in the named cell when you want it to dynamically refer to the today's date, or over-type e.g. "14/4/2016" into THE_DAY cell, when you want the conditional formatting to relate to a date in the past or in the future...

Does this work? It seems simple, maybe i have missed your point??

Let me know.

Hi -

Just use the following formula:

=DAYS(E3,TODAY())

Then apply conditional formatting (on the Styles section of the Home ribbon) to change the color of the cell when it is less than 30.

Hope this helps.

You're welcome. Glad we could help. Please don't forget to mark this thread as [SOLVED].

Thanks

Be aware that DAYS() calculates based on a 360-day year, so some results may surprise you. Also, it can return negative numbers, not absolute differences.

Hi SHG -

Are you sure? I don't see that in the documentation for the DAYS() function and I do see a separate function called DAYS360() based on 12 30 day months. Can you please verify that?

Thanks,

You're absolutely right -- I use Excel 2010, and DAYS it doesn't exist. Sorry.

No problem SHG! Thanks for checking!