Date expiration alert

**jrexcel1** · 05-13-2012, 02:48 PM

Hi all, I have seen this around but could not find exactly what I was looking for:

I would like to have a alert of expiration date 6 months in advance.

lets say that expiration date is March.23-2013...how would I enter a formula for expiration notice 6 months prior to this date?

Please in simple terms. I really suck in excel

Many thanks in advance

**Pepe Le Mokko** · 05-13-2012, 02:59 PM

Hi

you could use Conditional Formatting using

Please Login or Register  to view this content.

( where A1 contains the expiration date) as condition. Then format as needed

**jrexcel1** · 05-13-2012, 07:13 PM

Hi am not sure what I am suppose to do with the formula

lets say the date is march 13-2013 in cell B3

How would I get it to alert when the date is within 6 months. Very basic explanation please

**FDibbins** · 05-13-2012, 08:51 PM

highlight the range you want to apply the formatting to, then under format/conditional formatting (if i remember correctly, thats where it is in 2003, i use 2007), create a new rule...select "use formula" and enter the suggested formula from Pepe

**jrexcel1** · 05-13-2012, 09:05 PM

Thank you soooooooooooo much it worked. Now my question if I keep adding more dates to under the same column will it automatically recognize the formating in that column or do I have to select that cell too. Basically, once I select the enitre column, as I keep adding dates to the rows, will the formate be recognized under that column?

Also, I have another question about linking different worksheets? Can I pm you about that. I have big project due tuesday and so I low on time.

Many thanks

**jrexcel1** · 05-13-2012, 09:09 PM

I ran into a problem. That formula works for A1, but my data is in P column. How do I fix the formula for P...should I change A1 to P1?

**Pepe Le Mokko** · 05-14-2012, 02:19 AM

Originally Posted by jrexcel1

Also, I have another question about linking different worksheets? Can I pm you about that. I have big project due tuesday and so I low on time.

Many thanks

Please do not PM questions, so everybody can take advantage of the answer ( also see forum rules)

Now my question if I keep adding more dates to under the same column will it automatically recognize the formating in that column or do I have to select that cell too. Basically, once I select the enitre column, as I keep adding dates to the rows, will the formate be recognized under that column?

The easiest way is to select a large range, then enter a fisrt CF : =P1="" and add no format
Than enter the second condition and format as needed

**FDibbins** · 05-16-2012, 08:25 PM

as Pepe so politely suggested, yes, just change the reference in the CF formula

**jrexcel1** · 05-17-2012, 06:54 PM

I am having trouble people

Ook heres the problem: I want the formula to stay premenant on my excel, so when I click on the cell, the formula should appear. This conditional formatting seems to be something I have to do everyday or something I have to put in action when I want to look at the expiration days.

Second question: How do I change the formula to lets say, I want to know expiration dates that are 9months away from todays date (when i say today's date, I mean every single day the formula should recognize its a new day..today...tomorrow, day after tomorrow..etc..I hope this makes sense).

Which leads me to my next question. Lets say my column does not start at A1....but starts at M4, how do I write the formula for this M4 column.

Thank you in advance
p.s. you have to explain like you are explaing to your kids

**FDibbins** · 05-18-2012, 07:10 PM

CF works in the background, kinda invisibly.

ti apply it, there are various ways...
- before you apply CF, highlight the cells you want it to apply to.
- you can also, when you built the CF, point to the range you want it to apply to.
- or, you can just copy formats to wherever else (in the same column) you need it to be applied to.

its been a while since i worked on this 1, any chance of a sample workbook with dummy data (if the data is confidential)?
that way, i can set up the references for you and walk you through how they work