Subtracting military times (decimal format)

[dlang]

I apologize if this has been asked & answered many times before, bu
after reading for days in this forum, I can not find a direct answer t
my needs.

I have timecards that are already displayed in military/decimal format
I do not want to convert to hours/minutes and I'm not interested i
displaying results in hh:mm format. I do not want the date displayed.
simply want to calculate each employees total time worked for thei
shift's "start" to "end" (keeping the decimal format). Results are no
rounded...employees are paid for all fractional hours. There are no se
shifts, employees start/end at all times throughout a 24 hour tim
period. Some employees clock in/out for meals, others work straigh
through.

Examples are below, and if I've calculated correctly via calculator
the result that should be displayed in Column E for each employee i
also indicated...

Formatted as text:

Column A is "Start" (shift begin)
Column B is "Out" (lunch)
Column C is "In" (lunch)
Column D is "End" (shift end)
Column E is Total Time

Employee 1: (value in E1 should = 8.39 hours)
A1=21.92
B1=2.00
C1=3.00
D1=7.31
E1=

Employee 2: (value in E2 should = 4.01 hours)
A2=10.00
B2= (blank - no break)
C2= (blank - no break)
D2=14.01
E2=

Employee 3: (value in E3 should = 7.98)
A3=20.00
B3=00.00 (midnight -- not blank)
C3=00.99
D3=04.97
E3=

When I think I have the formula correct, it will work correctly on som
employees but not others. At this point I am totally brain dead....
can't remember what I've tried and what I haven't. I'm sure this is
simple calculation that I am just trying to make too complicated...

Thanks in advance!

 

[JulieD]

Hi

not sure i've got this right (i'm going cross-eyed with it too) but does

=IF(A1>D1,24+D1-A1,D1-A1)-(C1-B1)

work?

Cheers
JulieD

 

[Fred Smith]

If all times were on the same day, the formula is simply:

=d1-c1+b1-a1

But when the times span midnight, you need to add 24 hours to the formula.
So use:

=d1-c1+b1-a1+if(d1<a1,24,0)