excel+training - How do I

Resolved · Low Priority · Version Standard

Thu 20 Dec 2007, 17:00 Delegate Maria said...

Maria has attended:
Excel Intermediate course

How do I

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.

Thu 3 Jan 2008, 10:51 Trainer Rajeev said...

RE: How do I

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

Thu 3 Jan 2008, 12:11 Delegate Vicki said...

RE: How do I

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

Training courses