Microsoft Excel - How to formulate warnings when expiry date was given.

Asked By Noni Nordin on 13-Feb-12 04:24 AM
Hello,

I am using Excel 2010 pack. I have difficulties in creating the formula for my worksheet.

I want to know if we're given the date of issuance and it's expiry date, how to make a formula which will indicate how many days left before it get expire plus with the warning? How to make the expiry date column auto remind example in color? Let's say if it's about to be expiry in 10 days the column shall turn to red.

Above is the example how I want it to be formulated:

kalpana aparnathi replied to Noni Nordin on 13-Feb-12 04:27 AM
hi,

Try  with this example formula:

=EDATE(A2,VLOOKUP(B2,Y$2:Z$10,2,0))

Note that EDATE is an Analysis ToolPak function in Excel 2002. If the above doesn't work check that you have ToolPak installed

Tools > add-ins > tick "Analysis ToolPak"

Regards,
Somesh Yadav replied to Noni Nordin on 13-Feb-12 04:48 AM
One way of doing it is by using conditional formatting.  Select the cell(s) that you want to format, go to menu Format->Conditional Formatting, and enter a "Formula Is" condition with a formula similar to:
=(A1-TODAY())<30
Change the cell address to the selected cell.  You can use the Format button on the condition to define how you want the warning to be (red shade, for example).

Hope this helps,
Jatin Trikha replied to Noni Nordin on 13-Feb-12 05:17 AM
a combination of some excel function and it could be achived

try using conditional formatting, DateDif, and a additional column

also for getting the warning you can make use of macro.

hope this helps
Noni Nordin replied to kalpana aparnathi on 13-Feb-12 05:34 AM
Hi Kalpana,

Nothing seems to work :(
Donald Ross replied to Noni Nordin on 13-Feb-12 09:03 AM
Noni,

You simply need to change your date formatting to something excel can understand, I bet you are getting a
#VALUE! error. 

I formattted the date to custome yy/mm/dd and even tried to use =ABS( for the absolute value fo the cells but until i changed the date format so i could use =G4-today() it would not work. 

So look at your date format and then use Semesh's conditional formatting to change the color when it gets close to expire. 





HTH

Don