Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel+training - How do I
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
excel+training - How do I
Resolved · Low Priority · Version Standard
Reply Thu 20 Dec 2007, 17:00Delegate 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.
For upcoming training course dates see: Pricing & availability
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
Reply Thu 3 Jan 2008, 12:11Delegate 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
|