I have a staff plan of 220 people whose absence I want to add up.
In column A all names are below each other and the other columns consist of the weekdays, each divided into blocks of 2 working hours (so 4 per day for a working day of 8 hours).
These cells are dropdowns including all kinds of projects, but also ‘absent’.
The list is moved every week from ‘Week 0’ (current week) to ‘week -1’, and there is also a forecast ‘Week + 1’ (which then becomes ‘week 0’ every week)
From the moment that someone is absent (that can be at the beginning of the week, but also somewhere in the middle) I would like to see the name and the number of hours of absence in another column.
Because all weeks are archived in the same base, I would also like to add up the number of hours of absence, so that we can see a total of hours of absence after about 3 months.
Of course I have already tried to figure out how I can build this, but what I come across is the fact that it can be the same names. a linked record therefore only applies for 1 week.
Any suggestions how I can achieve this?