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]

Firstly dankjewel !!!

I'm getting a #Name? error now.

	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		#NAME?	######	#NAME?	#NAME?

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

T3_MONDAY​

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

<tbody>
</tbody>

 

[MarcelBeug]

Alsjeblieft!!

Sorry, REST must be MOD (1 translation overlooked...).

 

[mole999]

although 1/48 is valid, consider 0.02083 for 30 mins

 

[MarcelBeug]

although 1/48 is valid, consider 0.02083 for 30 mins

You make me curious: I can make sense of 1/48 (or 0.5/24) but 0.02083 doesn't ring a bell (and it equals 29:59.712, so a bit off).

So why 0.02083?

 

[mole999]

the end 3 is recouring 33333333 though i can't see how multiplying by 48 adds 6 hours

 

[LikeButtah]

I changed formulas. I'm getting close but we don't pay 10% night differential to the guys who work from 07:00. They just get a straight 8 hours at the reg rate because they are doing their reg day tour.

	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+10%	Reg	Reg+5%	Total Hrs
6	123 Main St				7:00	15:30	8:00		1:00	7:00	0:00	8:00

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

T3_MONDAY​

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

<tbody>
</tbody>

 

[MarcelBeug]

That poor guys

Reminds me of former ICT-HR challenges...

Please just explain the rules from HR perspective.

 

[deletedalien]

I'm just throwing this out there but....

if you're getting a negative value "dashes" then why not incorporate the ABS formula? might work...

HTH

 

[LikeButtah]

I'm just throwing this out there but....

if you're getting a negative value "dashes" then why not incorporate the ABS formula? might work...

HTH

When I apply ABS I get 3:00, which is then added on to the regular hours so this won't work for my application, but thanks for having me try something different that I never thought of.

 

[LikeButtah]

That poor guys

Reminds me of former ICT-HR challenges...

Please just explain the rules from HR perspective.

Don't worry about those guys, they do OK with all contract rules they have. Here are the rules that we need to take into account (That being said, please don't kill yourselves. I do really appreciate all the help given on this board. It is an awesome resource. The whole purpose of trying to get the calaculations correct is for the non-"normal" shifts that they might perform like 1300 X 1830 or some other off normal tour. I was hoping to not bog everyone down with HR stuff and was hoping it was a simple adjustment of the original formula.):

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 which gets the whole 10% and which is why I'm not even looking for a master all encompassing formula. I leaving them alone because currently if we have them work longer tours we just put them on the day tour roll call and the hours calculate correctly).

Thanks again for all the advice.