Excel/Tracking 401(k) Eligibility
QUESTION: Hi Richard,
I need to develop a spreadsheet that can manage eligibility dates of 1,000+ employees. The employees need to have 1 year of service before they are eligible. I need to have something that can alert me when they are 90 days away from that date, 45 days away, 30 days away and then the day that eligibility has been reached. Ideally I would like for these reminders to be color coded (cell filled) and remain that color until the next date has passed. For example, I would like for a 90-day warning to be in blue, 45-day in yellow, 30-day in red and the day eligibility has been reached in green (it can remain in green ongoing). Can you help???
Thanks in advance - I appreciate your time and efforts!
You can use the conditional formatting to get to what you want.
Here is what you do.
You have four conditions
Those whose eligible date is less than
If anything is more than 90 days you don't want any special format.
Assume the dates are in column A, starting with cell A1.
Make cell A1 the active cell.
Go to the toolbar/clic on conditional formatting/select new rule/select format only cells that contain/select "cell value"/then select from the next drop down box "less than or equal to"/then in the next box put in =today()+90/then clic on format and select the format that you want when the date is within 90 days from today/then clic ok.
Next with A1 still the active cell click on conditional formatting/then clic on manage rules/then clic on new rule then go thru the same process as in the previous except the formula is =today()+45, then clic ok then clic okay again. You should be back at the conditional formatting rules manager box. Clic on new rule then go thru the same process again with the <30 day rule and then with the 0 day rule which should be stated as =today().
After you finish then apply the rules and then click okay.
All of these conditional formats have been applied to cell A1 so to apply them to the rest of the cells that you need them in, use a copy then paste special/formats to the other cells.
Hope this helps.
---------- FOLLOW-UP ----------
QUESTION: Thanks Richard. To be more specific, I will need to spreadsheet to identify those dates based on 1 year from the date of employment, then backed up 90 days, 45 days, 30 days and 0 days (1 year). For example, if an employee was hired on 7/1/2012, they will be eligible on 7/1/2013. I will need the spreadsheet to identify 7/1/2013 - 90 days, 7/1/2013 - 45 days, etc. I am not sure how to set that up in the "new formatting rule" - this part of a spreadsheet is all new to me.
The simplest way to handle it would be to create a column that has the eligibility date (or one year from hire date in it) and use the same conditional formatting that we created previously and apply it to that column. In my previous answer I assumed that was what we were doing but if not then lets create that eligibility date. Assuming that cell A1 contains the hire date, a formula such as =A1+365 will give you the date one year from the hire date or the eligibility date. If you apply the previously created conditional formatting to that date you should get the result you want.
If you have not worked with dates much just remember that a date is simply a number to excel, so 7/1/12 is recognized as 41091 and if you add 365 days to that number it is 41456 or formatted as a date the result would be 7/1/13.