VB Code to confirm and add Total hours worked.

[rizmomin]

Hello:

Please refer to attached file.

I have clockIn-Out info for employees as shown.
I need a VB Code to update the # of numbers worked by employee after making adjusment in ClockIn-Out info.

Currently the data is retrieved in excel as shown.
Now after making changes in clockIn-Out info for employee, the corrected hours worked are as shown in
Range AC1:AK20.

I need to change data for all dates in cell for example: B3,B4,B9,B15,B16 and so on for 8/11/2014 so that it matches Range AC1:AK20.
Same for other date.

Let me know if you have any questions.
Thanks.

Riz

[xladept]

Hi Riz,

Try this:

Sub RizCounter(): Dim CI As Date, CO As Date, ET As Date, r As Long, c As Long
For c = 1 To Columns.Find("*", , , , xlByColumns, xlPrevious).Column Step 4
For r = 2 To Columns(c).Find("*", , , , xlByRows, xlPrevious).Row
        If InStr(1, Cells(r, c), "Clock") Then
CO = Cells(r, c + 2): CI = Cells(r, c + 1)
CO = CDate(Trim(Replace(CStr(CO), "1/1/1904", "")))
CI = CDate(Trim(Replace(CStr(CI), "1/1/1904", "")))
            ET = CO - CI
Cells(r - 3, c + 1).Resize(2, 1).NumberFormat = "h:mm"
    Cells(r - 3, c + 1).Resize(2, 1).Value = ET
        End If: Next r: Next c
End Sub

[rizmomin]

Hi xla:

Works fine, except small tweaking.
I need the hours worked as decimal, example 3:54 needs to be 3.90
Please let me know if you have any questions.
Thanks.

Riz

[xladept]

Hi Riz,

Maybe:

Sub RizCounter(): Dim CI As Date, CO As Date, ET As Date, r As Long, c As Long
Dim ES As Single
For c = 1 To Columns.Find("*", , , , xlByColumns, xlPrevious).Column Step 4
For r = 2 To Columns(c).Find("*", , , , xlByRows, xlPrevious).Row
        If InStr(1, Cells(r, c), "Clock") Then
CO = Cells(r, c + 2): CI = Cells(r, c + 1)
CO = CDate(Trim(Replace(CStr(CO), "1/1/1904", "")))
CI = CDate(Trim(Replace(CStr(CI), "1/1/1904", "")))
    ET = CO - CI: ES = Hour(ET) + Minute(ET) / 60
Cells(r - 3, c + 1).Resize(2, 1).NumberFormat = "0.00"
    Cells(r - 3, c + 1).Resize(2, 1).Value = ES
        End If: Next r: Next c
End Sub