Calculate Time

[whitethomas12]

Is there away to go through excel and sum the only the total hours
that someone has worked for each day

The format that my excel worksheet is:

Name1
12/15/2007 0:41 | Out
12/15/2007 0:44 | Out
12/15/2007 0:44 | IN
12/15/2007 0:44 | IN
12/15/2007 16:11 | IN
12/15/2007 16:18| Out
12/17/2007 9:03 | IN
12/17/2007 12:27 | OUT
12/17/2007 12:51 | IN
12/17/2007 17:08| Out

Etc for all users the "|" represents the next column

In the last column I have calculated all if the times by using the
following formula in a Macro

=A4-A3+IF(A3>A4,1)

But this gives me everything. Now I need to calculate just the times
that the person has worked throughout one day (it does not matter if
it is during a weekend; I just need the total hours)

 

[WLMPilot]

Your setup is a little confusing. It appears the person clocked IN twice at
the same time, clocked in and out at the same time. I don't know whatelse
you have in your speadsheet, but you might consider doing IN in one column
and OUT in the next column instead of both in the same column.

Here is the formula I used to calculate total hours worked (2 decimal place):
Here is my setup:
A1 = Date B1 = 8:00 (IN) C1 = 17:23 (OUT)
D1 = C1-B1 (9:23 in this example)
E1 = HOUR(D1) + (MINUTE(D1)/60) (9.38 hrs)

Maybe you can get what you need from this. Hope it helps.
Les

 

[WLMPilot]

Sorry, forgot you were working with a macro. I am not sure I am answering
your question, but the formula below will convert the to hours vs. hrs:mins
worked.
I also am currently working with a macro and the following was suggested to
me in order to get the time worked converted to hours, instead of
hours/minutes. I use a userform to input the time in/out, but I believe you
are pulling in that data from a spreadsheet.

ttltime2 = 24 * (CDate(TextBox3.Value) - CDate(TextBox2.Value))


Textbox3 = time clocked out
Textbox2 = Time clocked in.
DIM ttltime2 As Double

Les

 

[whitethomas12]

Sorry, forgot you were working with a macro.  I am not sure I am answering
your question, but the formula below will convert the to hours vs. hrs:mins
worked.
I also am currently working with a macro and the following was suggested to
me in order to get the time worked converted to hours, instead of
hours/minutes. I use a userform to input the time in/out, but I believe  you
are pulling in that data from a spreadsheet.

ttltime2 = 24 * (CDate(TextBox3.Value) - CDate(TextBox2.Value))

Textbox3 = time clocked out
Textbox2 = Time clocked in.
DIM ttltime2 As Double

Les









- Show quoted text -

Thank you for your help. What I decided to to do is create the
formula in the Macro. By doing this I do not have to worry about
calculating all of the IN times; excel will do this for me through the
formula once I find the range

Below is my code

Dim sFormula1 As String
Dim i As String
Dim j As String
Dim k As String
Dim Date1 As Date
Dim Date2 As Date
Range("C1").Select
Date1 = ActiveCell.Offset(0, -2)
Do While ActiveCell.Value <> ""
Date1 = ActiveCell.Offset(0, -2)
If ActiveCell.Offset(0, -2) = Date1 Then
Do While ActiveCell.Offset(0, -2).Value = Date1
If ActiveCell.Offset(0, -1).Value = "IN" Then
i = ActiveCell.Row

End If
j = i
If j <> "" Then
k = k & "," & "C" & j
If k = "," & "C" & j Then
k = "C" & j
End If
End If
j = ""
i = ""

ActiveCell.Offset(1, 0).Select
Loop
sFormula1 = "=SUM(" & k & ")"
ActiveCell.Offset(-1, 1).Value = sFormula1

End If
k = ""
ActiveCell.Offset(1, 0).Select
Loop

This is not probably the most perficient way but it seems to work.
The reason why I do have all of the time s seperated by IN and OUT is
because this report is recorded by our dorr badges ans we enter the
building. The excel file is just a data dump into a CSV file

If you do know an easier way to code this it would be greatly
appreciated

Once again I thank you for your help