Solved

Automatically Moving Time Punch Data

Posted on 2013-10-24
10
191 Views
Last Modified: 2013-10-31
I have a report that is exported from a time-clock program and I need to re-align the punch times.  The attached workbook has the data table with what are 3 typical variations of punches I see.

Lines 4 and 5:  These are standard daily punches, the punches occur on the same date the first line is the morning, the second line is after lunch.  On this one I need to move the second set of punches (in red text) to the end of the previous line (dark blue boxes)

Line 17: This punch is when someone forgets to punch out for lunch/break or works less than 5 hours.  For this punch I need to move that end of day punch (green text) to the end of that line (green box)

Lines 21 and 22:  This set of punches are for our graveyard shift and the first punch in is usually between 10:00pm and 11:59pm on one day (noted in black bold, 10/19/2013) and the remaining punches for that shift are actually the next day (noted in blue bold, 10/20/2013).  I need to move the second set of punches (brown text) to the end of the previous line (dark purple boxes)

Thank you,
Edwin
Punch-Extract-Report-2013-10-24.xls
0
Comment
Question by:gixxer1020
  • 5
  • 5
10 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 39599182
You didn't say whether you wanted a formula or a macro.

Here is a formula for cell H6 that may be copied down:
=IF(OR(AND($C5=$C6,OR($E5=$E6,$E5=$E6-1),F6=H5),F6=""),"",
IF(AND($C6=$C7,OR(AND($E6=$E7,$G7>$F7),$G6<$F6)),F7,""))

Here is a formula for cell I6 that may be copied down:
=IF(OR(AND($C5=$C6,OR($E5=$E6,$E5=$E6-1),G6=I5),G6=""),"",
IF(AND($C6=$C7,OR(AND($E6=$E7,$G7>$F7),$G6<$F6)),G7,IF(G6-F6>5/24,G6,"")))

The equivalent macro is:
Sub PunchCardReporter()
Dim rg As Range
With Worksheets("Sheet1")
    Set rg = .Range("E6")    'First Date
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp)).EntireRow    'All the data
End With
rg.Cells(1, "H").Formula = "=IF(OR(AND($C5=$C6,OR($E5=$E6,$E5=$E6-1),F6=H5),F6=""""),""""," & _
    "IF(AND($C6=$C7,OR(AND($E6=$E7,$G7>$F7),$G6<$F6)),F7,""""))"
rg.Cells(1, "I").Formula = "=IF(OR(AND($C5=$C6,OR($E5=$E6,$E5=$E6-1),G6=I5),G6=""""),""""," & _
    "IF(AND($C6=$C7,OR(AND($E6=$E7,$G7>$F7),$G6<$F6)),G7,IF(G6-F6>5/24,G6,"""")))"
rg.Columns("H").FillDown
rg.Columns("I").FillDown
rg.Columns("H").Formula = rg.Columns("H").Value
rg.Columns("I").Formula = rg.Columns("I").Value
End Sub

Open in new window


If you want to delete the rows where you moved data, I suggest AutoFilter on column I looking for empty strings (="") which look like blanks. Those rows can be deleted.
Punch-Extract-Report-2013-10-24Q.xls
0
 

Author Comment

by:gixxer1020
ID: 39599435
Thanks byundt,

Works great...can you move the data instead of copying it?

Thanks,
Edwin
0
 

Author Comment

by:gixxer1020
ID: 39599439
Oh, and I'll be using the Macro

Thanks,
Edwin
0
 
LVL 80

Expert Comment

by:byundt
ID: 39599518
Edwin,
I'd be glad to edit the macro to suit your needs.

Could you please post a file with a worksheet showing what the results ought to look like after the macro has run on your sample data? I ask because I'm not understanding why you would want to move the contents of F7:G7 into H6:I6 while leaving the rest of row 7 alone.

Brad
0
 

Author Comment

by:gixxer1020
ID: 39599542
Thanks Brad,

File attached.
Table below the original is the end result I'm going for...the sorting and elimination of extra rows i can do, its the code you wrote I had no idea of how to do

Thanks,
Edwin
Punch-Extract-Report-2013-10-24-.xls
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 80

Expert Comment

by:byundt
ID: 39599566
Edwin,
Here is the revised macro. I modified the formula for cell I6 to allow for the possibility that a person might work until the following day, but not do the punch out/in for their lunch break. This affects line 20. I also added code to delete the rows where data had been copied from.
Sub PunchCardReporter()
Dim rg As Range
Dim i As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
    Set rg = .Range("E6")    'First Date
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp)).EntireRow    'All the data
End With
rg.Cells(1, "H").Formula = "=IF(OR(AND($C5=$C6,OR($E5=$E6,$E5=$E6-1),F6=H5),F6=""""),""""," & _
    "IF(AND($C6=$C7,OR(AND($E6=$E7,$G7>$F7),$G6<$F6)),F7,""""))"
rg.Cells(1, "I").Formula = "=IF(OR(AND($C5=$C6,OR($E5=$E6,$E5=$E6-1),G6=I5),G6=""""),""""," & _
    "IF(AND($C6=$C7,OR(AND($E6=$E7,$G7>$F7),$G6<$F6)),G7,IF(MOD(G6-F6,1)>5/24,G6,"""")))"
rg.Columns("H").FillDown
rg.Columns("I").FillDown
rg.Columns("H").Formula = rg.Columns("H").Value
rg.Columns("I").Formula = rg.Columns("I").Value
For i = rg.Rows.Count To 1 Step -1
    If rg.Cells(i, "I").Value = "" Then rg.Rows(i).EntireRow.Delete
Next
End Sub

Open in new window

Brad
Punch-Extract-Report-2013-10-24Q.xls
0
 
LVL 80

Expert Comment

by:byundt
ID: 39599595
One more revision needed to the macro to handle the possibility that a person worked graveyard shift and went home before working 5 hours. Such a row should not be deleted even though columns H and I are blank.
Sub PunchCardReporter()
Dim rg As Range
Dim i As Long
Application.ScreenUpdating = False
With Worksheets("Sheet2")
    Set rg = .Range("E6")    'First Date
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp)).EntireRow    'All the data
End With
rg.Cells(1, "H").Formula = "=IF(OR(AND($C5=$C6,OR($E5=$E6,$E5=$E6-1),F6=H5),F6=""""),""""," & _
    "IF(AND($C6=$C7,OR(AND($E6=$E7,$G7>$F7),$G6<$F6)),F7,""""))"
rg.Cells(1, "I").Formula = "=IF(OR(AND($C5=$C6,OR($E5=$E6,$E5=$E6-1),G6=I5),G6=""""),""""," & _
    "IF(AND($C6=$C7,OR(AND($E6=$E7,$G7>$F7),$G6<$F6)),G7,IF(MOD(G6-F6,1)>5/24,G6,"""")))"
rg.Columns("H").FillDown
rg.Columns("I").FillDown
rg.Columns("H").Formula = rg.Columns("H").Value
rg.Columns("I").Formula = rg.Columns("I").Value
For i = rg.Rows.Count To 1 Step -1
    If (rg.Cells(i, "I").Value = "") And _
        (rg.Cells(i, "F").Value = rg.Cells(i - 1, "H").Value) And (rg.Cells(i, "G").Value = rg.Cells(i - 1, "I").Value) Then _
        rg.Rows(i).EntireRow.Delete
Next
End Sub

Open in new window

0
 

Author Comment

by:gixxer1020
ID: 39599758
One  last thing Brad,

On lines 17 and 21 the punches that you moved to the end were not deleted out of their original location.  Other than that the code is amazing

Thank you,
Edwin
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39603439
Edwin,
I added an ElseIf section to handle the case of column G value being moved to column I.
Sub PunchCardReporter()
Dim rg As Range
Dim i As Long
Application.ScreenUpdating = False
With Worksheets("Sheet1")
    Set rg = .Range("E6")    'First Date
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp)).EntireRow    'All the data
End With
rg.Cells(1, "H").Formula = "=IF(OR(AND($C5=$C6,OR($E5=$E6,$E5=$E6-1),F6=H5),F6=""""),""""," & _
    "IF(AND($C6=$C7,OR(AND($E6=$E7,$G7>$F7),$G6<$F6)),F7,""""))"
rg.Cells(1, "I").Formula = "=IF(OR(AND($C5=$C6,OR($E5=$E6,$E5=$E6-1),G6=I5),G6=""""),""""," & _
    "IF(AND($C6=$C7,OR(AND($E6=$E7,$G7>$F7),$G6<$F6)),G7,IF(MOD(G6-F6,1)>5/24,G6,"""")))"
rg.Columns("H").FillDown
rg.Columns("I").FillDown
rg.Columns("H").Formula = rg.Columns("H").Value
rg.Columns("I").Formula = rg.Columns("I").Value
For i = rg.Rows.Count To 1 Step -1
    If (rg.Cells(i, "I").Value = "") And _
        (rg.Cells(i, "F").Value = rg.Cells(i - 1, "H").Value) And (rg.Cells(i, "G").Value = rg.Cells(i - 1, "I").Value) Then
        rg.Rows(i).EntireRow.Delete
    ElseIf rg.Cells(i, "G").Value = rg.Cells(i, "I").Value Then
        rg.Cells(i, "G").ClearContents
    End If
Next
End Sub

Open in new window

Brad
Punch-Extract-Report-2013-10-24Q.xls
0
 

Author Closing Comment

by:gixxer1020
ID: 39615905
Thanks Brad....works like a champ

Edwin
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now