Calculating length of employment based on the hired date and current date

admin February 13, 2010 Uncategorized Comments Off

Question

I have a table of data containing an employee name and the date they were hired, how can I get Excel to calculate the length of employment of each employee based on the date they were hired ?

Answer :

Well suppose your excel table looks like this :

Employee_Name Hired_date
J Smith 10/07/1980
A Jones 6/03/2008
R Shah 12/12/2007
B Chen 15/02/2009
I Holmes 1/01/2010
** Note : **

We are going to be using the database DATEDIFF function. The column containing the date value must be named
something ending in _DATE in order for QueryCell to realise it contains Date data. (see Options in QueryCell for more information).

You could run the following SQL in QueryCell to provide the number of days each employee has been employed :

SELECT
  EMPLOYEE_NAME,
  HIRED_DATE,
  DATEDIFF(DAY, HIRED_DATE, CURRENT_DATE) AS EMPLOYED_DAYS
FROM
  A

This SQL run in QueryCell will provide the table :

EMPLOYEE_NAME HIRED_DATE EMPLOYED_DAYS
J Smith 10/07/1980 10811
A Jones 6/03/2008 710
R Shah 12/12/2007 795
B Chen 15/02/2009 364
I Holmes 1/01/2010 44

You could run the following SQL in QueryCell to provide the number of months each employee has been employed :

SELECT
  EMPLOYEE_NAME,
  HIRED_DATE,
  DATEDIFF(MONTH, HIRED_DATE, CURRENT_DATE) AS EMPLOYED_MONTHS
FROM
  A

This SQL run in QueryCell will provide the table :

EMPLOYEE_NAME HIRED_DATE EMPLOYED_MONTHS
J Smith 10/07/1980 355
A Jones 6/03/2008 23
R Shah 12/12/2007 26
B Chen 15/02/2009 12
I Holmes 1/01/2010 1

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.