using DATEDIF to figure years of service in Excel

For our employee birthdays and anniversaries message, I get the data from HR in a spreadsheet. But it’s pretty raw, a mere list of employee names & their dates of hire. People like to know how many years of service are being celebrated, so I’ve worked out the following formula.

=DATEDIF(VALUE(C4),”1-Jan-2008″,”y”) & ” y “

Notes:

• The fomula is by default DATEDIF(startdate,enddate,unit)
• The start date variable (hire date) is in cell C4. However, since I actually concatenated columns that included Month, Day, and Year into a string, and strings don’t work for calculations, I needed to get the VALUE of C4.
• The end date for comparison should be the first day of the following month. You could use the variable Now(), which tells the system to use the current date and time, but then the number of years would be off for a few folks.
• I’m only looking for years of service, so I use the unit code “y”. This is in quotes so as not to be confused with the Y column.
• To make it clear to readers that this number is a year, I added the string &” y” to the end of the result.
• This formula is copied down a new column for all employee names & dates.
• The spreadsheet is sorted by hire date.
• Then I just copy & paste the cells into a message blast to our employees.

You could also use this formula to calculate age, but I wouldn’t recommend sending that information out to a world where many people prefer not to reveal their age!

You could also use this formula to calculate project time, using the NETWORKDAYS function. For more tips on this and other Date functions in Excel, see OfficeArticles.com.