Hi,
I am working with employee records and I am trying to calculate years in company.
As many employees are working have short term contracts I have many entries/rows per employee with hire and termination date.
For each employee I want to have in a single row the employee's earlier hire date and latest termination date.
How can I do that in excel query?
Many thanks in advance
Kat
Hi Kat,
It would help if you could upload a file resembling your real one, though without real persons names. That way, it's easier to provide a solution.
R
Thank you R for asking that!
It made me realize that my question was not correct.
These are my data:
| Name | ID | Hire Date | Term Date | Contract Duration (months) | Position |
| Kat Bs | 123 | 1/1/2020 | 3/1/2020 | 2 | Assistant Waiter |
| Kat Bs | 123 | 2/1/2022 | (today) | 6 | Assistant Waiter |
| Jonh Smith | 456 | 1/1/2022 | 2/1/2022 | 1 | Assistant Waiter |
| Jonh Smith | 456 | 2/2/2022 | 5/1/2022 | 2 | Head Waiter |
So in fact what I need is to calculate the sum of months per employee.
I believe you can just group by Name (or ID) summing the Contracts Duration column. But to be sure, how would you want to summarise the date you provided?
Thank you very much!
I wasn't aware of the grouping feature! It works great for me!
If possible, could you please also tell me how I can keep only rows for which the Term date was within the last week (or month)?rnEvery week I want to run a query giving me a list of employees leaving the company within the last week.
