Yet, another TIME problem

[Jmcb1b]

Okay, I will attempt to explain my situation without just confusin
anyone who reads this.

I have a time sheet. It consists of the following fields:
Time Started/Lunch Begin/Lunch End/Time Ended/Total Time/Over Time

In the first four fields the cells are number format (looks lik
military time but in reality it isn't) ie: 0630/1130/1230/1600

In the fifth field, the total time is a decimal value. It always read
8.00 or less and carries the remainder,if any, to the over time fiel
which is also decimal value. The overtime has to be reported in quarte
hour increments ie: .15, .25, .50, .75.

The requirments needed:
The time in the first four fields cannot be enetered with the colon.
The time in the other two fields have to show up as decimal values an
be in quarter hour increments. Through the help of people using thi
site, I've almost completed my timesheet journey but I have a bug i
the formulas and really need assistance because I am once agai
stumped.

Problem:
Due to the first four fields not being in a time format, "some" result
return and error. ie: 0630/1130/1230/1600 returns 8.75 when it shoul
return 8.50 for time purposes. This seems like it should be somethin
simple that I'm overlooking.

Formulas using:
for hours worked cell:
=IF(((F16-E16)+(D16-C16))/100>8, SUM(8),((F16-E16)+(D16-C16))/100)

for overtime cell:
=TRUNC(IF((F16-E16)+(D16-C16)>800,(F16-E16)+(D16-C16)-800,0)/100)+IF(MOD(IF((F16-E16)+(D16-C16)>800,(F16-E16)+(D16-C16)-800,0)/100,1)<0.15,0,IF(MOD(IF((F16-E16)+(D16-C16)>800,(F16-E16)+(D16-C16)-800,0)/100,1)<0.3,0.25,IF(MOD(IF((F16-E16)+(D16-C16)>800,(F16-E16)+(D16-C16)-800,0)/100,1)<0.45,0.5,IF(MOD(IF((F16-E16)+(D16-C16)>800,(F16-E16)+(D16-C16)-800,0)/100,1)<1,0.75))))

The first four cells are formatted to 0###

The "hours worked" and "over time" are formatted to number with
decimal places.

Any help is greatly appreciated. I thank all who take the time to hel
me with this problem.

Any other questions, please email: (e-mail address removed)

 

[Peo Sjoblom]

For simplicity assume that start is A1 and end D1 lunch is B1 to C1

=INT(D1/100)+MOD(D1,100)/60-INT(A1/100)+MOD(A1,100)/60-(INT(C1/100)+MOD(C1,1
00)/60-INT(B1/100)+MOD(B1,100)/60)

will return a decimal value of 8.5 using your example

 

[Jmcb1b]

I think I'm on the right track here. The formula helped but it threw ou
some values that were erroneous. I incorporated your formula into m
equation and for an unknown reason on my part it begins to coun
backwards when the answer equals anything over 8.00.

example:
0700/1130/1200/1600 should = 8.50 hours (should display 8.00)
But it's result is 7.50 instead.


This is the formula that's giving me the problem:

=IF(INT(F10/100)+MOD(F10,100)/60-INT(C10/100)+MOD(C10,100)/60-(INT(E10/100)+MOD(E10,100)/60-INT(D10/100)+MOD(D10,100)/60)>8,SUM(8),(F10/100)+MOD(F10,100)/60-INT(C10/100)+MOD(C10,100)/60-(INT(E10/100)+MOD(E10,100)/60-INT(D10/100)+MOD(D10,100)/60))

Please help. Thanks