Log In
Register
Home
Active Member
August 9, 2022
Offline
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
See Answer
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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
Active Member
August 9, 2022
Offline
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.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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?
Answers Post
Active Member
August 9, 2022
Offline
Thank you very much!
I wasn't aware of the grouping feature! It works great for me!
Active Member
August 9, 2022
Offline
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.
1 Guest(s)
