Time Clock Issues

I am trying to use excel to update our time sheets.... I work in dispatch and all our drivers use time cards to clock in and out.. At the end of the day I enter those times into our payroll.

I am trying to make the process easier but I have hit a road block...

Our time cards show times as military time in the HOUR portion, but in the minute portion it goes to the 100th... so for example... 11:45 at night shows up as 23.75.. this is a problem. I like to think of myself as excel savvy but this is the difficult part....

Driver comes in at 23.75 and delivers his shipment comes back and clocks out a 05.83 in the morning.

So I have 23.75 in A1 and 05.83 in B1

C1 needs to show total hours as 6.08

It needs to recognize that days have changed over to be able to calculate total... I cannot after hours of looking things up and trying different formulas to get it to recognize this... It's on the tip of my brain, but I give up. I need help... Anyone up to the challenge?

A1 = 21.75
B1 = 5.83
C1 = "=IF(A1>B1, 24-A1+B1, A1-B1)"

C1 has current formula as provided but result is C1 = 1:55

Needs to show 6.08 as the total

Any other ideas?

That took care of the issue with the change over... but it doesn't work.

The formula works for that specific row but not the column...

A1 21.78 clock in B1 05.93 clock out C1 needs to supply the change over.... which the formula above does....

HOWEVER

the next DAY:

A2 08.73 B2 19:21 C2 = -10.48

This needs NOT to be a " - " (negative sign) in front of the number so my calculations are right.... HOW?

It needs to be able to calculate same day totals... as well as late night to next day totals...

Please advise... Thank you!

I have a problem

Our time clock registers time to the .99 not .59 so this makes things difficult... for example

21.75 is 21:75 8.50 is 8:30 etc, etc

Okay.. thats the first problem... but no big deal figured that part out =sum(B1-A1)

The problem I am having... the turnover from day to day

7/10/11 8.45 17.88 9.43
7/11/11 6.35 15.33 8.98
7/12/11 21.75 4.52 -17.23
7/13/11 7.58 9.75 2.17
7/14/11 5.15 18.45 13.30
7/15/11 20.89 6.45 -14.44
7/16/11 0.00
7/17/11 0.00
7/18/11 0.00
7/19/11 0.00
7/20/11 0.00
7/21/11 0.00
7/22/11 0.00
7/23/11 0.00

Total Hours 2.21

ABOVE = using =sum(B1-A1)

Obviously this messes up the turnover from day to day as the 7/12 and 7/15 are incorrect hours

So I tried this...

7/10/11 8.45 17.88 -9.43
7/11/11 6.35 15.33 -8.98
7/12/11 21.75 4.52 6.77
7/13/11 7.58 9.75 -2.17
7/14/11 5.15 18.45 -13.30
7/15/11 20.89 6.45 9.56
7/16/11 0.00
7/17/11 0.00
7/18/11 0.00
7/19/11 0.00
7/20/11 0.00
7/21/11 0.00
7/22/11 0.00
7/23/11 0.00

Total Hours -17.55

Using =IF(A1>B1, 24-A1+B1, A1-B1)

It recognizes the change over but no longer recognizes the DAY time hours... while the numbers are right as far as hours... the "-" (negative sign) is throwing my numbers off.

What formula can I use for the DAY time as well as the TURNOVER?

Any ideas... been working on this for 2 days now and can't come up with a solution... Please Advise!!

Try this:

If your data looks like this:

PHP Code: 

        A           B        C
1) 7/10/2011     23.75     5.83
2) 7/11/2011      6.35    15.33
3) 7/12/2011     21.75     4.52
4) 7/13/2011      7.58     9.75
5) 7/14/2011      5.15    18.45
6) 7/15/2011     20.89     6.45 


In cell D1 enter the formula: =IF(C1>B1,C1-B1,(C1+24)-B1)
drag down as many cells as needed.

Try this

Create 3 columns containing the following data:
Date (column A), Time In (column B), Time Out (column C).
In the 4th column (Number of Hours), enter the following formula:
=(C2-B2+(C2
To sum the total number of hours worked, use the SUM function as shown in the following formula:
=SUM(D2:D7)

Hallelujah!!! You have solved my issue. Thank you for your help... It's much appreciated.