contentgrrl

I am conTENT. My work is CONtent.

using DATEDIF to figure years of service in Excel

Posted by contentgrrl on December 19, 2007


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.

About these ads

3 Responses to “using DATEDIF to figure years of service in Excel”

  1. Great formula. With a slight modification, I’ll be able to implement your formula for my job. Thanks!

  2. flash said

    Thanks that was a excellent read.

  3. Tiffany said

    Great help! Thank you for sharing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: