I have to do formulas in which i need to have the expiry date but to let me know 3 months in advance from the expiry that that is due to expire.

Dear Maria

Thank you for attending the Excel Intermediate Course. I hope you enjoyed the training. Calculating dates is always an interesting topic.

Excel treats Dates as Numbers. By Default MS Excel assumes 01/01/1900 to be 1 and then keeps on adding 1 to every day from there onwards. That is how it is able to perform calculations using dates.

E.g. If cell A1 has 01/01/08 and in cell B1 you typed a formula =A1+30 you will get a result 31/01/08 in Cell B1 as 30 is added to 01/01/08.

I am not clear if you are using a formula to calculate the expiry date or not.

Please refer to the attachment Excel file that I have uploaded to the below mentioned explanation.

Cell A2, A3 and A4 have exact dates i.e. 12/01/08 which is supposed to be current date.
Cells B2, B3 and B4 have dates 11/04/08, 13/04/08 and 10/04/08 respectively.
In cell C2 you will observe “About to expire” because if you observe the formula in the formula bar is =IF(B2=(A2+90),"About to Expire","").

Although the same formula is in cell C3 =IF(B3=(A3+90),"About to Expire","") and C4 =IF(B4=(A4+90),"About to Expire","") but their result appears to be blank in those cells.

I hope this is what you were trying to get from your particular scenario.

If this has helped you in resolving your query please mark this question as resolved. If, however, you have further query then please use the reply button and ask me to either clarify or please specify your request!!

Kindest Regards

Rajeev Rawat
MOS Master Instructor 2000/2003

Maria

I had the same problem with a training matrix which needed to let me know in advance of when training would expire.

I used conditional formatting to show yellow for due to expire and red once it had exceeded the due date. To do this you will need to create the spreadsheet with today() in a cell for all formatting to compare to and I hid 2 rows which held details of the course 'licence life' and the period of notice.
e.g. in cell A1 place the TODAY() formula - this will ensure that every time you open the sheet the calculations will work to todays date. Then (assuming it is rows 2 & 3 that you are hiding) in row 2 enter in each column the amount of days for the licence ie if 2 years 'validity period' make figure 728, then in row 3 'booking period' if 2 months notice needed then enter 60.

Using these (including the TODAY() cell)as absolute cells in your conditional formatting and selecting yellow as warning of booking notice period and red for expired this should give you what you require.

Hope this helps
Vicki