Something like this?
At top of sheet, you can set buffer time (100 Hrs in your case), and number of normative working hours in workday.
When the number of normative working hours can wary depending on date - e.g. in case in your country is practiced shorter workday before holidays - then you need a calendary sheet, where the number of normative hours will be given for every calendary day, and in worktime table you'll have additional column where normative hours for WorkStart date are calculated.
For Time format, the custom format "[h]:mm" is used. This format displays hours egual or greater then 24 Hrs. All other Time formats show only less than 24 Hrs.
Columns WH, DB and TB are helper columns (you can hide them), where the number of hours is calculated in General format. Those columns are needed, because fields with date/Time formats return error whenever the calculation returns 0 or negative value. So all totals must be calculated using numeric values, and for final result those numbers are converted back to Hrs (TotalBalance column).
I added a column OffHrs - there you can enter e.g. lunch time, in case it doesn't count as work time
This table will work until TotalBalance for any table row remains > 0
|