Spreadsheet: 803572d1667557033-accumulating-time-based-on-time-values-in-another-cell-trial-flexi
Sheet: Mar 22 Updated

 |  |  | Dublin Port Health |  |  |  |  |  |  |  |  |  |  | Name | Team 1
 |  |  | [NAME] |  |  |  |  |  |  |  |  |  |  | Month | March | 2022 |  | Notes
 |  |  |  |  |  |  |  |  |  |  |  | Target
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | All sheets will be in 28 day increments
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | There will 4 versions of the sheet starting with a different pattern depending on the team
 |  |  |  | SHIFT |  |  |  |  |  | OUT OF HOURS meeting |  | Credit | Credit/Debit |  | Totals |  |  | Hours are set to record start time, break/lunch times and finish time
# | Shift | Weekday | Date | Start day | Lunch | Back from lunch | Break | Back from break | Finish day | Start meeting | End meeting | Leave | TOIL | Comments | Daily time | TOIL |  | TOIL is set as hours worked outside of rostered time e.g. attending a meeting for 2 hours during the day when on a rest or night shift
1 | Rest | Monday | 2022-02-20 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 10:00:00 | 12:00:00 |  |  |  |  |  |  | Cell N5 is taken from the previous month total which will be Cell R39
2 | Rest | Tuesday | 2022-02-21 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 |  |  |  |  |  |  | Tried to overcome one day into another with the MOD function but didn't really work!
3 | Rest | Wednesday | 2022-02-22 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 |  |  |  |  |  |  | Where a days/half days leave is taken it should be recorded in Cell N43
4 | Day | Thursday | 2022-02-23 00:00:00 | 6:55:00 | 12:00:00 | 12:45:00 | 16:00:00 | 16:20:00 | 19:05:00 | 0:00:00 | 0:00:00 |  |  |  |  |  |  | It would be great is this could be done with a formula!
5 | Day | Friday | 2022-02-24 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 11:05:00 |  | Annual Leave |  |  |  | TOIL accrued is in Cell R42, TOIL taken R43. TOIL left in R45 should move the next sheet
6 | Day | Saturday | 2022-02-25 00:00:00 | 6:55:00 | 12:00:00 | 12:45:00 | 16:00:00 | 16:20:00 | 19:05:00 | 0:00:00 | 0:00:00 |  |  |  |  |  |  | Target hours are 39 per week or 156 per 28 day period. This includes accumulating 2 flexi hours per week as the true target is 37 hours
7 | Day | Sunday | 2022-02-26 00:00:00 | 6:55:00 | 12:00:00 | 12:45:00 | 16:00:00 | 16:20:00 | 19:05:00 | 0:00:00 | 0:00:00 |  | 4:00:00 | Toil taken |  |  |  | A flexi day arises when the total target exceeds 234 hours (this will happen over 2 sheets or every 6 weeks.
8 | Rest | Monday | 2022-02-27 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00
9 | Rest | Tuesday | 2022-02-28 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00
10 | Rest | Wednesday | 2022-03-01 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00
11 | Rest | Thursday | 2022-03-02 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00
12 | Rest | Friday | 2022-03-03 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 |  |  |  |  |  |  | The 2 hours in CellQ8 should be in R8
13 | Rest | Saturday | 2022-03-04 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 |  |  |  |  |  |  | Cell L12 should recognise that leave was taken instead of a shift being worked
14 | Rest | Sunday | 2022-03-05 00:00:00 | 10:00:00 | 12:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 |  |  |  |  |  |  | I'm not sure how to fix cell R9 - the TOIL formula will change depending on whether it is [NAME], Day or Night
15 | Night | Monday | 2022-03-06 00:00:00 | 18:55:00 | 0:00:00 | 0:45:00 | 5:00:00 | 5:20:00 | 7:05:00 | 0:00:00 | 0:00:00 |  |  |  |  |  |  | Cell Q22 ?
16 | Night | Tuesday | 2022-03-07 00:00:00 | 18:55:00 | 0:00:00 | 0:45:00 | 5:00:00 | 5:20:00 | 7:05:00 | 0:00:00 | 0:00:00
17 | Night | Wednesday | 2022-03-08 00:00:00 | 18:55:00 | 0:00:00 | 0:45:00 | 5:00:00 | 5:20:00 | 7:05:00 | 0:00:00 | 0:00:00
18 | Night | Thursday | 2022-03-09 00:00:00 | 18:55:00 | 0:00:00 | 0:45:00 | 5:00:00 | 5:20:00 | 7:05:00 | 0:00:00 | 0:00:00
19 | Rest | Friday | 2022-03-10 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00
20 | Rest | Saturday | 2022-03-11 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00
21 | Rest | Sunday | 2022-03-12 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00
22 | Day | Monday | 2022-03-13 00:00:00 | 6:55:00 | 12:00:00 | 12:45:00 | 16:00:00 | 16:20:00 | 19:05:00 | 0:00:00 | 0:00:00
23 | Day | Tuesday | 2022-03-14 00:00:00 | 6:55:00 | 12:00:00 | 12:45:00 | 16:00:00 | 16:20:00 | 19:05:00 | 0:00:00 | 0:00:00
24 | Day | Wednesday | 2022-03-15 00:00:00 | 6:55:00 | 12:00:00 | 12:45:00 | 16:00:00 | 16:20:00 | 19:05:00 | 0:00:00 | 0:00:00
25 | Rest | Thursday | 2022-03-16 00:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00 | 0:00:00
26 | Night | Friday | 2022-03-17 00:00:00 | 18:55:00 | 0:00:00 | 0:45:00 | 5:00:00 | 5:20:00 | 7:05:00 | 0:00:00 | 0:00:00
27 | Night | Saturday | 2022-03-18 00:00:00 | 18:55:00 | 0:00:00 | 0:45:00 | 5:00:00 | 5:20:00 | 7:05:00 | 0:00:00 | 0:00:00
28 | Night | Sunday | 2022-03-19 00:00:00 | 18:55:00 | 0:00:00 | 0:45:00 | 5:00:00 | 5:20:00 | 7:05:00 | 0:00:00 | 0:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  | Net Hours Worked this period
 |  |  |  |  |  |  |  |  |  |  |  |  |  | Flexitime credit/debit carried forward
 |  |  | Signed
 |  |  | Approved
 |  |  |  |  |  |  |  |  |  |  |  |  |  | Toil this month (toll accrued)
 |  |  |  |  |  | Hours owed | 222 |  |  |  |  |  |  | Toil from previous month |  | 15:00:00
 |  |  |  |  |  | Hours taken |  |  |  |  |  |  |  | Toil taken
 |  |  |  |  |  | Balance |  |  |  |  |  |  |  | Total toil (toll left)