Time Formula

[LikeButtah]

My workbook has 21 sheets. One for each of the 3 tours for each day of the week. I would like to have a formula that I could place in either the day tour "T2" or 3 X 11 "T3" sheets in I6, J6 and K6 to be able to properly calculate time. The midnight shift only has the formula in K6 because we pay full 10% night differential for the entire tour not just after 0:00 hours. We don't pay for the 1/2 hour meal period each day and we pay at the regular rate until 1800 hours when the rate goes to reg + 5% and then when it turns to midnight we pay at the reg + 10% rate (Except for the midnight shift).

Currently when I change the time in and time out to let's say 0700 X 1530 I get an error "######" in J6, the reg +5%. In G6 the calculation is good but in I6 it shows 8:30 instead of 8:00. Also if I input 0700 X 1830 it gives me 11:00 in I6 when I should have 10:30 in I6 and :30 in J6.

If there is a way to calculate this better by adding a helper column or just with a different formula it would be great. Thanks.

	A	B	C	D	E	F	G	H	I	J	K
4	5/18/2015	Monday	Week Ending:	5/24/2015					18:00	0:00	8:00
5	POST	OFFICER	RADIO #	MEAL	TIME IN	TIME OUT	HOURS WORKED		Reg	Reg+5%	Reg+10%
6	123 Main St				15:00	23:30	8:00		3:00	5:00	0:00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

T3_MONDAY​

Worksheet Formulas Cell Formula G6 =IF(F6=E6,0,IF(F6-E6<0,F6+1-E6-1/48,F6-E6-1/48)) I6 =IF(E6>I$4, 0, IF(F6>=E6, IF(F6-I$4>0, I$4-E6, F6-E6), IF(F6+1-I$4>0, I$4-E6, F6+1-E6))) J6 =IF(AND(E6=0,F6=0),0,IF(I$4>E6,IF(J$4+1-F6<=0.5,F6-I$4-1/48,J$4-I$4+1-1/48),IF(J$4+1-F6<=0.5,F6-E6-1/48,J$4-E6+1-1/48))) K6 =IF(AND(E6=0,F6=0),0,IF(J$4+1-F6>0.5, F6, 0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[LikeButtah]

A few questions from my side:
1. What are the possible time frames from begin to end? I mean minimum / maximum?
2. What is the minimum hours worked to calculate 0:30 hours break? E.g. if hours in the office is 1:00, I guess there is no break. What is the treshold?
3. If there is a break, from what interval would that be subtracted? E.g. someone is in the office from 14:00-22:00, is the break before are after 18:00? Or split?

I really appreciate you guys looking into this.

The possible time frames are the entire 24 hour period. A person could theoretically work 18 or even 24 hours.
The minimum hours worked would be 4 hours since this is the minimum amount of time contractually that they can be assigned.
The time interval is subtracted from the end time. If someone works until 23:30 the 5% night differential would be 5 hours 30 min but we pay the 5% until 23:00 hence 5:00 in J6.

 

[mole999]

if your going to exceed 24 hours you should really include the date to help excel, 24:01 and they will calculate for 1 minute

 

[LikeButtah]

if your going to exceed 24 hours you should really include the date to help excel, 24:01 and they will calculate for 1 minute

Historically we have never exceeded 24 hours. Normally the most time worked is 18 hours. But we do break into the next day when someone works from 07:00 to 02:00.

 

[mole999]

did you try this formula

use =F6+(F6 < E6)-E6 to produce the hours

with those values

 

[LikeButtah]

did you try this formula

use =F6+(F6 < E6)-E6 to produce the hours

with those values

Yes, In G6 this formula works =F6+(F6 < E6)-E6-(1/48) to produce the correct total hours. I will be changing my longer formula to your more compact one, but the real issue is the breakdown of hours in I6, J6 and K6.

 

[MarcelBeug]

My 2 cents:

Hours worked in cell G2:

=MAX(0,MOD(F2-E2,1)-1/48)

For the split over time intervals, I adjusted the sequence to 0:00 in I1, 8:00 in J1, 18:00 in K1 and I added a helper in L1: 1 (or 24:00 with format [h]:mm).

This will allow for a uniform formula in I2, copied to the right to J2 and K2, and copied for all data rows.
Update: I will take another look since I didn't notice my questions were answered.
Note: the following formula does not take into account the 0:30 hours break.

So in I2:

=($E2<$F2)*(MAX(0,MIN($F2,J$1)-MAX($E2,I$1)))+($E2>$F2)*(MAX(0,J$1-MAX($E2,I$1))+MAX(0,MIN($F2,J$1)-I$1))

 

[MarcelBeug]

Thanks for the answers to my questions.
Basically I subtracted 0:30 break from the end time ($F2): MOD($F2-1/48;1).

So the hours worked in G2 become:

=MAX(0,MOD(MOD(F2-1/48,1)-E2,1))

And for the hours per interval (mind the adjusted sequence: 0:00 in I1, 8:00 in J1, 18:00 in K1 and I added a helper in L1: 1 (or 24:00 with format [h]:mm)):
In I2, copied to the right to J2 and K2, and copied down for all data rows:

=($E2 < MOD($F2-1/48,1))*(MAX(0,MIN(MOD($F2-1/48,1),J$1)-MAX($E2,I$1)))+($E2 > MOD($F2-1/48,1))*(MAX(0,J$1-MAX($E2,I$1))+MAX(0,MIN(MOD($F2-1/48,1),J$1)-I$1))

 

[LikeButtah]

This is what I'm getting in I6, J6 and K6. But I should be getting all 8 hours into I6 since the tour worked does not meet the 18:00 hurdle.

	A	B	C	D	E	F	G	H	I	J	K	L
1									0:00	8:00	18:00	24:00
2	ROLL CALL TOUR :		Three
3	DATE	DAY	Tour Hours:	3:00 PM - 11:30 PM
4	5/18/2015	Monday	Week Ending:	5/24/2015
5	POST	OFFICER	RADIO #	MEAL	TIME IN	TIME OUT	HOURS WORKED		Reg	Reg+5%	Reg+10%	Total Hrs
6	123 Main St				7:00	15:30	8:00		1:00	7:30	0:00	8:30

<tbody>
</tbody>

T3_MONDAY​

Worksheet Formulas Cell Formula G6 =MAX(0,MOD(F6-E6,1)-1/48) I6 =($E6<$F6)*(MAX(0,MIN($F6,J$1)-MAX($E6,I$1)))+($E6>$F6)*(MAX(0,J$1-MAX($E6,I$1))+MAX(0,MIN($F6,J$1)-I$1)) J6 =($E6<$F6)*(MAX(0,MIN($F6,K$1)-MAX($E6,J$1)))+($E6>$F6)*(MAX(0,K$1-MAX($E6,J$1))+MAX(0,MIN($F6,K$1)-J$1)) K6 =($E6<$F6)*(MAX(0,MIN($F6,L$1)-MAX($E6,K$1)))+($E6>$F6)*(MAX(0,L$1-MAX($E6,K$1))+MAX(0,MIN($F6,L$1)-K$1)) L6 =SUM(I6:K6) <tbody> </tbody>

<tbody>
</tbody>

 

[MarcelBeug]

Hi,

See my latest formulas.

I had to make some corrections due to < and > were interpreted as HTML code and additionally I'm translating from Dutch to English and I use the separator ; instead of ,

Should be okay now.


Cheers,

Marcel

 

[MarcelBeug]

This is what I'm getting in I6, J6 and K6. But I should be getting all 8 hours into I6 since the tour worked does not meet the 18:00 hurdle.

Start time is 7:00 so 1 hour is between 0:00 and 8:00.

Please notice that you should adjust your fields I5, J5 and K5 to the new sequence, so "Reg+10%", "Reg", "Reg +5%" respectively.