# Time Formula

#### LikeButtah

##### Board Regular
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.

ABCDEFGHIJK
45/18/2015MondayWeek Ending: 5/24/201518:000:008:00
6123 Main St15:0023:308:003:005:000:00

</tbody>
T3_MONDAY

Worksheet Formulas
CellFormula
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))

</tbody>

<tbody>
</tbody>

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### LikeButtah

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

#### mole999

##### Well-known Member
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

##### Board Regular
Here is the the error I'm seeing

Excel 2012
ABCDEFGHIJKL
45/18/2015MondayWeek Ending: 5/24/201518:000:008:00
5POSTOFFICERRADIO #MEALTIME INTIME OUTHOURS WORKEDRegReg+5%Reg+10%Total Hrs
6123 Main St7:0015:308:008:30########0:005:30

</tbody>
T3_MONDAY

Worksheet Formulas
CellFormula
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)

</tbody>

<tbody>
</tbody>

Last edited:

#### LikeButtah

##### Board Regular
Thanks for looking at this.

Excel 2012
ABCDEFGHIJKL
45/18/2015MondayWeek Ending:5/24/201518:000:008:00
5POSTOFFICERRADIO #MEALTIME INTIME OUTHOURS WORKEDRegReg+5%Reg+10%Total Hrs
6123 Main St7:0018:3011:0011:000:000:0011:00
T3_MONDAY
Cell Formulas
RangeFormula
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)

Last edited:

#### mole999

##### Well-known Member
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

##### Board Regular
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

##### New Member
thank you fao looking this.
Excel 2012
ABCDEFGHIJKL
45/18/2015MondayWeek Ending:5/24/201518:000:008:00
5POSTOFFICERRADIO #MEALTIME INTIME OUTHOURS WORKEDRegReg+5%Reg+10%Total Hrs
6123 Main St7:0018:3011:0011:000:000:0011:00

</tbody>
T3_MONDAY

#### mole999

##### Well-known Member
#### is a time value that evaluates as negative

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

#### MarcelBeug

##### Well-known Member
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?

Last edited:

Replies
2
Views
214
Replies
13
Views
391
Replies
12
Views
171
Replies
6
Views
111
Replies
4
Views
169

1,195,644
Messages
6,010,896
Members
441,571
Latest member
stolenweasel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back