Time Formula

LikeButtah

Board Regular
Joined
Jun 24, 2011
Messages
168
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
5POSTOFFICERRADIO #MEALTIME INTIME OUTHOURS WORKEDRegReg+5%Reg+10%
6123 Main St15:0023:308:003:005:000: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
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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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
 
Upvote 0
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

<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
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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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

<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
 
Upvote 0
#### is a time value that evaluates as negative

use =F6+(F6 < E6)-E6 to produce the hours
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top