[SOLVED] format cells yyyymmdd and then conditional format

**joesport5** · 08-02-2010, 01:12 PM

Hi I am trying to establish within a cell to read the date as 20100802 (YYYYMMDD) from this I would like it to automatically change to orange 90 days out, yellow at 60 days out, and red at 30 days out.

**Whizbang** · 08-02-2010, 01:34 PM

Attached is an example workbook.

Essentially, to display as YYYYMMDD, simply go to "Format" -> "Cells" -> "Number" tab and select "Custom". Enter "YYYYMMDD" into the box provided and click "Ok".

To apply the conditional formatting, go to "Format" -> "Conditional Formatting". You'll need three seperate conditions, so click "Add >>" twice.

All conditions will be "Cell value is" and "greater than or equal to". The first formula will be "=TODAY()+90". The second formula will be "=TODAY()+60" and the third will be "=TODAY()+30".

Click "Format on each of the three conditions, navigate to the "Patterns" tab and select the appropriate color. Click "ok" to confirm.

Then click "Ok" again to confirm the whole shebang.

**joesport5** · 08-02-2010, 01:50 PM

I understand all of your post and even looked (cut and paste) onto mine, however everytime I tried to enter a date I get nothing but ################. I am entering it 20100802

**Whizbang** · 08-02-2010, 01:52 PM

Enter it 08/02/2010. It will redisplay as 20100802.

**joesport5** · 08-02-2010, 02:16 PM

Thanks that works

**sean2minu** · 08-02-2010, 04:01 PM

hey. thanks for this solution! Is there a way to utilize a same function and make a window pop-up when a date is about a week away?

I work in a office of Respiratory Therapists and would like to incorporate a function to alert me when licenses are close to expiring.

Any advice would be AWESOME! Thanks in advanced!