Good day dear Expert,
For a work project I need to sum the total time an employee spends together with a colleague on a specific site in a month.
Some sites are worked alone while others are together with someone else. They do not always start and end at the same time on a specific site. Could be that one has been working already for an hour when the colleague arrives and leaves again before the other one ends his shift ect.
The time stamps are detailed to within a quarter of an hour.
I have a full list of all the employees and their Ins&Outs per day on the different sites, problem is that this is for a huge company and a months worth of data gives me 70K+ lines.
Hence why posting this in the VBA section, a macro seems to be the only way to be able to calculate a solution without exploding my pc.
I have worked the file and found a semi solution but, as the file is huge, and calculations include multiple countifs and sumifs, it takes ages to calculate.
My solution at this time was to split up the day in timestamps of half an hour (=48 column) in which I check:
- Has the worker worked in a specific timestamp (ex. 15:30 - 16:00) then 1 otherwise 0
- pivot table per site per day for each timestamp to have to total number of people working on a site in each timestamp
- Check per worker if he has worked in a timestamp and if there were +1 people active during that timestamp that day on that project to sum half an hour to his 'total time worked in group'
Some extra points of detail:
- a worker can work on multiple sites within a day
- he can also have multiple shift on a day on the same site (ex. 10:00-12:00 and 16:00-18:00) => these will show as 2 lines in the raw data
What would you need as sample - extra information?
I've added a sample sheet of data with which I have to work and a column with the desired outcome, to clarify what I'm looking for in cas that was not clear.... This does not include the calculations I have come up with myself as noted above, don't think they would help a lot but sure can if required, unfortunately my VBA knowledge is close to 0 at the moment so cannot share a lot on that front...
Thank you for the help, more than welcome to answer your questions to be more detailed!
Greetz, Robin
Bookmarks