Combination of 12 Excel Employee files (1 per month) - yearly average for ees who joined mid-year

[Galag2612]

Hello,

I'm trying to compute data based on 12 HRIS monthly files.

The goal: calculate the yearly working time per employee (between 0 and 1 - 0: the employee did not work this year; 1: the employee worked full time). 

I manage to combine the 12 file into 1 data set and compute average working time. The problem is for employees that have joined during the year. As they are not listed in the first files, the average calculation is made on working time for active months, which is not showing real prorated working time:

Example: an employee joined full-time in October 2022, worked 3 months full time.

Yearly working time = 3 / 12 = 0.25. 

In this case, it's calculating 3/3 = 1 - since the employee is not listed in the first 9 files.

Could you please advise?

Thank you very much in advance,

Martin

[olgad]

Hi, here is my data and calculation

[olgad]

Hi, if you create a calculated column for each employee, 1/12, then if you sum up, for those who did one year it will be one, but for those who didnt it will calculate to 3/12

[Galag2612]

Hi olgad,

Thank you for replying. I'm not sure I understand the solution though:
The data I have is a combination of 12 files, meaning that per employee that was active during the whole 22 period, I have 12 lines of data. For those who were there half of 2022, I only have 6 lines so I'm not able to calculate on the 6 missing lines to have them at 0 for example.