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]

I'm guessing that there is no clear way to do this.

 

[mole999]

I'm a tad confused on your needs, you describe ######### as a problem, but not display the issue in the graphic

any chance of a few more viewable lines of the top with and without errors

 

[LikeButtah]

Here is the the error I'm seeing



Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L
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%	Total Hrs
6	123 Main St				7:00	15:30	8:00		8:30	########	0:00	5:30

<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 A4 =+T1_MONDAY!A4:A4 B4 =TEXT(A4,"DDDDDDDDDD") D4 =T1_MONDAY!D4 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)) L6 =SUM(I6:K6) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[LikeButtah]

ADVERTISEMENT

Thanks for looking at this.

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L
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%	Total Hrs
6	123 Main St				7:00	18:30	11:00		11:00	0:00	0:00	11:00
T3_MONDAY

Cell Formulas
Range	Formula
A4	=+T1_MONDAY!A4:A4
B4	=TEXT(A4,"DDDDDDDDDD")
D4	=T1_MONDAY!D4
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))
L6	=SUM(I6:K6)

 

[mole999]

IF(I$4>E6,IF(J$4+1-F6<=0.5,F6-I$4-1/48,J$4-I$4+1-1/48

so i4 = 18:00
e6 = 07:00

evaluates as true

therefore

J$4+1-F6<=0.5

j4 = 0:00 + 1 (add 24 hours)
-f6 = 15:30

result is 8:30 (which is less than 0.5)

so we should evaluate

F6-I$4-1/48

f6 = 15:30 - 1 (which i think produces the negative time) hence the hashes

then divided by 48

If you try F6-I$4-(1/48) does that resolve it, PEDMAS might explain it



I use
=IFERROR(O5+(O5<N5)-N5,"")
O = latest
N = Earliest

to accommodate for midnight hours

can't be more insightful at the moment

 

[LikeButtah]

ADVERTISEMENT

Thanks for taking a look at it but placing the (1/48) does not solve the error. Also wrapping the nested If's in an IFERROR doesn't remove the ####. Maybe I need to start fresh with maybe MOD for time calc. Thanks again.

 

[shumayma]

thank you fao looking this.
Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L
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%	Total Hrs
6	123 Main St				7:00	18:30	11:00		11:00	0:00	0:00	11:00

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody style="border-top-style: none;">
</tbody>

T3_MONDAY​

 

[mole999]

#### is a time value that evaluates as negative

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

 

[MarcelBeug]

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 or after 18:00? Or split?