Hello all. I am trying to put a sheet together to calculate my working hours and how many hours I have left.
The company I work for assigns us with 2239 hours to work in a year. We are issued with 248 hours of holidays per year which reduces the hours needed to work to 1991 (at times some hours are carried over, etc which could increase the hours, but this is easy to calculate.
As per the example below, we work 4 days for 12.3 hours per day (the green highlighted cells) and then have 4 days off. Each 12.3 hours day reduces the total amount of hours left by 12.3 hours. My end goal with the sheet below is to calculate when I will finish (as well as keep track of hours worked, etc.)
When other people need to take leave, I am able to work a cover shift for them which would reduce my hours by 12.3 - this is planned in advance. This is shown as CVR.
When somebody calls in sick, I am able to work a short-notice call back for them which would reduce my hours by 18.3. This is shown as CB.
If I use holiday hours, this would reduce my holiday hour total and thus extend my finish date. This is shown as PAL.
What I am asking for help with is this - I want the range below to populate with the green 12.3 cells on the days that I am scheduled to work until my Hours Remaining is at zero. In the example below, I would finish all 12.3 hour shifts on the 11th June 2021 and have to work 10.7 hours on the 12th June 2021 but if I were to take away all of the CB or CVR cells, I would like the sheet to add more 12.3 hours shifts as required to show how many days I would need to work. As I add more CVR or CB or PAL cells, I'd like the amount of 12.3 cells to reduce.
How high is the mountain I am trying to climb?
Thanks in advance.
The company I work for assigns us with 2239 hours to work in a year. We are issued with 248 hours of holidays per year which reduces the hours needed to work to 1991 (at times some hours are carried over, etc which could increase the hours, but this is easy to calculate.
As per the example below, we work 4 days for 12.3 hours per day (the green highlighted cells) and then have 4 days off. Each 12.3 hours day reduces the total amount of hours left by 12.3 hours. My end goal with the sheet below is to calculate when I will finish (as well as keep track of hours worked, etc.)
When other people need to take leave, I am able to work a cover shift for them which would reduce my hours by 12.3 - this is planned in advance. This is shown as CVR.
When somebody calls in sick, I am able to work a short-notice call back for them which would reduce my hours by 18.3. This is shown as CB.
If I use holiday hours, this would reduce my holiday hour total and thus extend my finish date. This is shown as PAL.
What I am asking for help with is this - I want the range below to populate with the green 12.3 cells on the days that I am scheduled to work until my Hours Remaining is at zero. In the example below, I would finish all 12.3 hour shifts on the 11th June 2021 and have to work 10.7 hours on the 12th June 2021 but if I were to take away all of the CB or CVR cells, I would like the sheet to add more 12.3 hours shifts as required to show how many days I would need to work. As I add more CVR or CB or PAL cells, I'd like the amount of 12.3 cells to reduce.
How high is the mountain I am trying to climb?
Thanks in advance.
