You are here:

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!

ANSWER: Robert

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
 90 days
 45 days
 30 days
  0 days
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.

Thanks again!


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.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Richard Roberts


Can assist you in most areas of Excel, have been working with it for about 15 years in many types of applications, but primarily in financial and accounting applications. I am a CPA and many client or client problems have necessitated the use of excel. I am not an expert in charting, macros, or pivot tables.


Have been working with Excel for about 20 years primarily in accounting and financial areas.

BA, Certified Public Accountant

©2016 All rights reserved.