Hi all,
Sorry for the oversize table. I couldn't work out how to get it narrower.
I have the following table that I need to develop a formula for that produces the results in column E.
Column B is text, C is decimal time and D is formatted [h]:mm. Column E, “time worked”, is currently calculated manually based on an industrial award. The calculations are based on what is effectively 2 different shifts, one being “night” and the other being normal.
The other factors are based on a maximum of 15- and 30-minute (night) cumulative time frames and singular times greater than 15 or 30 minutes.
Column D is summed until it reaches a total of no more than 15 minutes. At this point 15 minutes is entered in to column E. An example of this is in lines 1, 2 and 3.
Cells D1 and D2 only total 5 minutes, however cell D3 would bring the total to 16 minutes which, according to the industrial award, is not permitted and the 11 minutes must be included in another 15-minute period. At this point “0:15” is entered into cell E2 and the calculations start anew in the next row (3). This takes us to D3. It shows 11 minutes and D4 would take the total to 30 minutes. Once again not permitted so “0:15” is entered into cell E3. Cell D4 is a singular time of 19 minutes so “0:19” is entered into cell E4.
The same processes apply for the “night” shift except that “0:30” minutes is used as the limits instead of “0:15”. Examples are in line 7 being a singular “night” time of 18 minutes that would require “0:30” to be entered into cell E7 and in lines 13 to 18. Cells D13 to D18 have cumulative total of 12 minutes, however this requires “0:30” to be entered into cell E18.
My main problem is getting the additions to restart after the entry of the required time in column E.
I am reasonably OK with basic functions and nested formulas etc but I get a bit lost with the higher end functions and where/when to use them. Any help or ideas would greatly appreciated.
Cheers and thanks in advance,
Graeme
Sorry for the oversize table. I couldn't work out how to get it narrower.
I have the following table that I need to develop a formula for that produces the results in column E.
A | B | C | D | E |
1 | | 0.07 | 0:03 | |
2 | | 0.04 | 0:02 | 0:15 |
3 | | 0.19 | 0:11 | 0:15 |
4 | | 0.33 | 0:19 | 0:19 |
5 | | 0.04 | 0:02 | |
6 | | 0.06 | 0:03 | 0:15 |
7 | night | 0.31 | 0:18 | 0:30 |
8 | | 2.43 | 2:25 | 2:25 |
9 | | 0.17 | 0:10 | 0:15 |
10 | | 0.40 | 0:23 | 0:23 |
11 | | 1.30 | 1:18 | 1:18 |
12 | | 0.57 | 0:34 | 0:34 |
13 | night | 0.04 | 0:02 | |
14 | night | 0.04 | 0:02 | |
15 | night | 0.05 | 0:02 | |
16 | night | 0.03 | 0:01 | |
17 | night | 0.05 | 0:03 | |
18 | night | 0.04 | 0:02 | 0:30 |
19 | | 0.19 | 0:11 | 0:15 |
20 | | 0.14 | 0:08 | |
21 | | 0.11 | 0:06 | 0:15 |
Column B is text, C is decimal time and D is formatted [h]:mm. Column E, “time worked”, is currently calculated manually based on an industrial award. The calculations are based on what is effectively 2 different shifts, one being “night” and the other being normal.
The other factors are based on a maximum of 15- and 30-minute (night) cumulative time frames and singular times greater than 15 or 30 minutes.
Column D is summed until it reaches a total of no more than 15 minutes. At this point 15 minutes is entered in to column E. An example of this is in lines 1, 2 and 3.
Cells D1 and D2 only total 5 minutes, however cell D3 would bring the total to 16 minutes which, according to the industrial award, is not permitted and the 11 minutes must be included in another 15-minute period. At this point “0:15” is entered into cell E2 and the calculations start anew in the next row (3). This takes us to D3. It shows 11 minutes and D4 would take the total to 30 minutes. Once again not permitted so “0:15” is entered into cell E3. Cell D4 is a singular time of 19 minutes so “0:19” is entered into cell E4.
The same processes apply for the “night” shift except that “0:30” minutes is used as the limits instead of “0:15”. Examples are in line 7 being a singular “night” time of 18 minutes that would require “0:30” to be entered into cell E7 and in lines 13 to 18. Cells D13 to D18 have cumulative total of 12 minutes, however this requires “0:30” to be entered into cell E18.
My main problem is getting the additions to restart after the entry of the required time in column E.
I am reasonably OK with basic functions and nested formulas etc but I get a bit lost with the higher end functions and where/when to use them. Any help or ideas would greatly appreciated.
Cheers and thanks in advance,
Graeme
