Hello all,
I am really hoping that someone would be kind enough to help with this as I have been trying to figure out how to do this for weeks (literally!).
I have attached the problem with some example data and the intended output table. I've tried to simplify the original problem as much as I can in order to make it easier to explain.
What I have is a list of Job Start dates and an estimate of the time (in days) for the duration of that job (i.e. either 30 or 60). What I am trying to do is calculate the number of days in each month that the job has taken. So for example, if a job will take 60 days and it starts on the 28/06, then there will be 2 days recorded in June, 31 in July and 27 in August. The sum of days per job for each month is to be calculated for each Area and Job type.
I was hoping that I could do this as a sumproduct using the existing range names, also in addition to the number of calendar days I want to do a calculation for working days as well.
I would be very thankful if someone could help and perhaps explain how a solution works.
Many many thanks!