Formula or VBA Employee Head Count by Minute By Account

Felix1980

New Member
Joined
May 16, 2018
Messages
37
Hello, My problem is as follows : I have a data dump from our time keeping software which shows data like this :

Employee NameDateEmployee #In PunchLabor AccountOrg JobWork RuleOut PunchPaycodeStart TimeHoursDaysMoneySourceCommentNoteSourceNameAmountSpanShiftDailyCumulative
Employee 112/14/2020107207:53AM020/281/2810/001/02/9125/-12:01PM4:014:01
Employee 112/14/20201072012:29PM020/281/2810/001/02/9125/-4:45PM4:148:158:158:15
Employee 112/14/202010720020/281/2810/001/02/9125/-Out PunchLate Out0:15
Employee 112/15/2020107207:55AM020/281/2810/001/02/9125/-12:02PM4:0212:17
Employee 112/15/20201072012:29PM020/281/2810/001/02/9125/-5:20PM4:488:508:5017:05
Employee 112/15/202010720020/281/2810/001/02/9125/-Out PunchLate Out0:50
Employee 112/16/2020107207:55AM020/281/2810/001/02/9125/-12:02PM4:0221:07
Employee 112/16/20201072012:29PM020/281/2810/001/02/9125/-4:55PM4:238:258:2525:30
Employee 112/16/202010720020/281/2810/001/02/9125/-Out PunchLate Out0:25
Employee 112/17/2020107207:56:00 AM020/281/2810/001/02/9125/-12:03:00 PM4:0329:33
Employee 112/17/20201072012:28PM020/281/2810/001/02/9125/-5:01PM4:288:318:3134:01
Employee 112/17/202010720020/281/2810/001/02/9125/-Out PunchLate Out0:31
Employee 112/18/2020107207:55AM020/281/2810/001/02/9125/-12:05PM4:0538:06
Employee 112/18/20201072012:31PM020/281/2810/001/02/9125/-4:46PM4:118:168:1642:17
Employee 112/18/202010720020/281/2810/001/02/9125/-Out PunchLate Out0:16
Employee 212/14/2020106055:25PM020/218/2182/001/08/5142/-5:28PM
Employee 212/14/2020106055:28PM/214/2140/001////214/2140/001///6:58PM1:281:28
Employee 212/14/2020106056:58PM/218/2182/001////218/2182/001///9:17PM2:193:47
Employee 212/14/2020106059:44PM020/218/2182/001/08/5142/-9:51PM0:043:51
Employee 212/14/2020106059:51PM/214/2142/001////214/2142/001///11:14PM1:235:145:145:14
Employee 212/14/202010605020/218/2182/001/08/5142/-Out PunchEarly Out2:46
Employee 212/15/2020106055:24PM020/218/2182/001/08/5142/-5:25PM5:14
Employee 212/15/2020106055:25PM/218/2182/001////218/2182/001///5:57PM0:275:41
Employee 212/15/2020106055:57PM/214/2140/001////214/2140/001///6:32PM0:356:16
Employee 212/15/2020106056:32PM/218/2182/001////218/2182/001///8:06PM1:347:50
Employee 212/15/2020106058:06PM/214/2142/001////214/2142/001///9:47PM1:414:174:179:31
Employee 212/15/202010605020/218/2182/001/08/5142/-Out PunchEarly Out4:13
Employee 212/16/2020106055:26PM020/218/2182/001/08/5142/-5:27PM9:31
Employee 212/16/2020106055:27PM/218/2182/001////218/2182/001///9:32PM4:0213:33
Employee 212/16/2020106059:56PM020/218/2182/001/08/5142/-11:22PM1:205:225:2214:53
Employee 212/16/202010605020/218/2182/001/08/5142/-Out PunchEarly Out2:38
Employee 212/17/2020106055:25PM020/218/2182/001/08/5142/-5:26PM14:53
Employee 212/17/2020106055:26PM/218/2182/001////218/2182/001///10:23PM4:534:534:5319:46
Employee 212/17/202010605020/218/2182/001/08/5142/-Out PunchEarly Out3:37
Employee 212/18/2020106055:25PM020/218/2182/001/08/5142/-5:27PM19:46
Employee 212/18/2020106055:27PM/218/2182/001////218/2182/001///5:28PM19:46
Employee 212/18/2020106055:28PM/214/2140/001////214/2140/001///5:56PM0:2620:12
Employee 212/18/2020106055:56PM/218/2182/001////218/2182/001///7:19PM1:2321:35
Employee 212/18/2020106057:19PM/214/2140/001////214/2140/001///8:09PM0:5022:25
Employee 212/18/2020106058:09PM/218/2182/001////218/2182/001///9:35PM1:2623:51
Employee 212/18/20201060510:04PM/214/2142/001////214/2142/001///12:28AM2:236:286:2826:14
Employee 312/14/2020114635:27AM020/212/2120/001/03/4350/-10:29AM4:594:59
Employee 312/14/20201146310:54AM020/212/2120/001/03/4350/-1:15PM2:167:157:157:15
Employee 312/15/2020114635:22AM020/212/2120/001/03/4350/-10:15AM4:4512:00
Employee 312/15/20201146310:27AM020/212/2120/001/03/4350/-3:11PM4:269:119:1116:26
Employee 312/15/202011463020/212/2120/001/03/4350/-Out PunchLate Out1:11
Employee 312/16/2020114635:25AM020/212/2120/001/03/4350/-10:34AM5:0421:30
Employee 312/16/20201146310:57AM020/212/2120/001/03/4350/-1:51PM2:477:517:5124:17
Employee 312/17/2020114635:22AM020/212/2120/001/03/4350/-10:52AM5:2229:39
Employee 312/17/20201146311:13AM020/212/2120/001/03/4350/-1:41PM2:197:417:4131:58
Employee 312/17/202011463020/212/2120/001/03/4350/-Out PunchEarly Out12:06
Employee 312/18/2020114635:19AM020/212/2120/001/03/4350/-10:32AM5:0237:00
Employee 312/18/20201146310:55AM020/212/2120/001/03/4350/-2:00PM2:588:008:0039:58

For every in and out punch in a day there is a line. The Org Job column shows the labour account they are working in at that time. I created a sheet with every minute of a day across columns and Column A having the date and Column B the account number.

1608736117283.png


My thought was to add up employees by minute in each account so we can see the employee movements through the day across the accounts. I first thought I would do this by putting a countifs formula in the cells, however quickly realized a sheet with that many formulas would probably crash as it tried to process. I then started looking at VBA and can figure how to get If the row is this date and this account..... I stall however on trying to figure out how to add 1 to that particular cell (account / minute) across the columns between their start and end time.

I don't want to create a "lookup" type situation where I would have to put the start and end time :

1608735902459.png

Since I want to show patterns with this data. I need to be able to look at this and ask a supervisor about their employee counts vs their output on this day compared to another.

If someone could point me towards the proper code to put data in certain "minute" columns if the employees time is between this and that, I would surely appreciate it.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

Alternative to VBA could be a Power Query solution. I loaded from table (converting your range of data in a table) but a better solution might be to start from a new workbook and load from file-from Excel. I hope my assumption is right, that "Span" is worked time.
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    FilterSpan = Table.SelectRows(Source, each ([Span] <> null)),
    RemoveCols = Table.SelectColumns(FilterSpan,{"Employee Name", "Date", "In Punch", "Labor Account", "Span"}),
    ChangeTypewithLocale_Date = Table.TransformColumnTypes(RemoveCols, {{"Date", type date}}, "en-US"),
    ChangeType_Text = Table.TransformColumnTypes(ChangeTypewithLocale_Date,{{"Labor Account", type text}}),
    ChangeType_Times = Table.TransformColumnTypes(ChangeType_Text,{{"Date", type date}, {"In Punch", type time}, {"Span", type time}}),
    ListWorkedTimes = Table.AddColumn(ChangeType_Times, "WorkedTimes", each List.Times([In Punch],Number.From([Span])*24*60, #duration(0,0,1,0))),
    #"Expanded WorkedTimes" = Table.ExpandListColumn(ListWorkedTimes, "WorkedTimes"),
    #"Grouped Rows" = Table.Group(#"Expanded WorkedTimes", {"Date", "Labor Account", "WorkedTimes"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"
Load this query as a pivot table
1609068593936.png


Personally I would group worked time frame per 15 minutes at the least. Even 30 minutes or 1 hour grouping would already show a pattern I guess.
 

Felix1980

New Member
Joined
May 16, 2018
Messages
37
Thank you for the reply GraH. That would work but I want to be able to see by employee by minute. This way I can change the data further at a later date, such as adding their wages per minute, etc.
I have patched a "working" code together, however it takes a long time to run considering it has to go through, on average 8000 lines for just a one week data dump. If anyone can make this work more quickly, or sees redundancies (it started to get a bit confusing), I would appreciate the help.

VBA Code:
Sub GrabMins()
'Takes times from Time Detail Spreadsheet and puts it into Prod Minutes sheet
Dim coll As Object
Dim Timecoll As Object
Dim SourceName As String
Dim SourceNameDate As String
Dim SourceDate As Date
Dim SourceTime As Date
Dim SourceAcct As String
Dim SourceEmpNum As Integer
Dim SourceRng As Range
Dim DestRng As Range
Dim DestRng2 As Range
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim Sourcews As Worksheet
Dim Destws As Worksheet
Dim DestLRow As Long
Dim SourceLRow As Long
Dim DestReturn As Integer
Dim TimeDiff As Integer
Dim StartTime As Integer
Dim EndTime As Integer
Dim AddRng As Range
Dim ExtraAddTime As Range
'Dim ExtraAddRng As Range
Dim AddTime As Range

Set Sourcewb = Workbooks("Time Detail (Spreadsheet Export) (5).xlsm")
Set Destwb = Workbooks("Prod Minutes.xlsm")
Set Sourcews = Sourcewb.Sheets("Detail")
Set Destws = Destwb.Sheets("HeadPerMin")
Set coll = CreateObject("System.Collections.ArrayList")
Set Timecoll = CreateObject("System.Collections.Arraylist")
SourceLRow = Sourcews.Cells(Sourcews.Rows.Count, "A").End(xlUp).Row
DestLRow = Destws.Cells(Destws.Rows.Count, "A").End(xlUp).Row

'This collection is for all the names / dates in the destination workbook  Need to add a find last used row here yet
For Each DestRng In Destws.Range("A1:A" & DestLRow)
    coll.Add Destws.Cells(DestRng.Row, 1).Value & Destws.Cells(DestRng.Row, 3).Value
Next DestRng

'This collection is for all the Times across the top of the destination workbook
For Each DestRng2 In Destws.Range("D1:BCM1")
    Timecoll.Add CDate(Destws.Cells(1, DestRng2.Column).Value)
Next DestRng2

'This is to find if the row from the source book exists in the destination book
For Each SourceRng In Sourcews.Range("A2:A" & SourceLRow)
    
    SourceNameDate = Sourcews.Cells(SourceRng.Row, 1).Value & Sourcews.Cells(SourceRng.Row, 2).Value
    SourceName = Sourcews.Cells(SourceRng.Row, 1).Value
    SourceDate = Sourcews.Cells(SourceRng.Row, 2).Value
    SourceTime = Sourcews.Cells(SourceRng.Row, 4).Value
    SourceAcct = Sourcews.Cells(SourceRng.Row, 6).Value
    SourceEmpNum = Sourcews.Cells(SourceRng.Row, 3).Value
    DestReturn = coll.indexof(SourceNameDate, 0)
    If DestReturn = 0 Then
        DestReturn = 1
    End If
    TimeDiff = DateDiff("n", Sourcews.Cells(SourceRng.Row, 4), Sourcews.Cells(SourceRng.Row, 8))
    StartTime = Timecoll.indexof(CDate(SourceTime), 0)
    EndTime = StartTime + TimeDiff
    
    'If Time column is empty then go to next row
    If SourceTime = 0 Then
        GoTo NextSourceRng
    End If
        
    'If the search in collection doesn't return anything then
    If DestReturn = -1 Then
        'Find next empty row
        DestLRow = Destws.Cells(Destws.Rows.Count, "A").End(xlUp).Row + 1
        'Enter Name, Emp# and Date in first 3 columns
        Destws.Range("A" & DestLRow).Value = SourceName
        Destws.Range("B" & DestLRow).Value = SourceEmpNum
        Destws.Range("C" & DestLRow).Value = SourceDate
        'Add Name/Date to collection
        coll.Add SourceNameDate
        'Re-run SourceNameDate Search to change output for next line
         DestReturn = coll.indexof(SourceNameDate, 0)
        'Set the time range that is between the start and end dates on that line
        'If end time is a smaller number that start time then
        If EndTime < StartTime Then
            'Set first time range
            Set AddRng = Destws.Range(Destws.Cells(DestReturn + 1, StartTime + 4), Destws.Cells(DestReturn + 1, 1443))
            'For each cell in first time range put the labour account
            For Each AddTime In AddRng
                AddTime.Value = SourceAcct
            Next AddTime
            'Add 1 day to the original date
            SourceDate = SourceDate + 1
            'Create a new SourceNameDate
            SourceNameDate = SourceName & SourceDate
            'Run the search for the new SND
            DestReturn = coll.indexof(SourceNameDate, 0)
            If DestReturn = 0 Then
                DestReturn = 1
            End If
            'If new SND is not found in collection then
            If DestReturn = -1 Then
                'Find empty row again
                DestLRow = Destws.Cells(Destws.Rows.Count, "A").End(xlUp).Row + 1
                'Enter employee data again for next date
                Destws.Range("A" & DestLRow).Value = SourceName
                Destws.Range("B" & DestLRow).Value = SourceEmpNum
                Destws.Range("C" & DestLRow).Value = SourceDate
                'Set 2nd time range
                Set ExtraAddRng = Destws.Range(Destws.Cells(DestLRow, 4), Destws.Cells(DestLRow, EndTime + 4))
                'For each cell in second time range put labour account
                For Each ExtraAddTime In ExtraAddRng
                    ExtraAddTime.Value = SourceAcct
                Next ExtraAddTime
                'Add new SND to collection
                coll.Add SourceNameDate
                'Run the search for the new SND again
                DestReturn = coll.indexof(SourceNameDate, 0)
                If DestReturn = 0 Then
                    DestReturn = 1
                End If
            Else
                'Set 2nd time range
                Set ExtraAddRng = Destws.Range(Destws.Cells(DestReturn + 1, 4), Destws.Cells(DestReturn + 1, EndTime + 4))
                'For each cell in second time range put labour account
                For Each ExtraAddTime In ExtraAddRng
                    ExtraAddTime.Value = SourceAcct
                Next ExtraAddTime
            End If
        Else
            Set AddRng = Destws.Range(Destws.Cells(DestReturn + 1, StartTime + 4), Destws.Cells(DestReturn + 1, EndTime + 4))
            For Each AddTime In AddRng
                AddTime.Value = SourceAcct
            Next AddTime
        End If
    Else
        If EndTime < StartTime Then
            Set AddRng = Destws.Range(Destws.Cells(DestReturn + 1, StartTime + 4), Destws.Cells(DestReturn + 1, 1443))
            For Each AddTime In AddRng
                AddTime.Value = SourceAcct
            Next AddTime
            'Add 1 day to the original date
            SourceDate = SourceDate + 1
            'Create a new SourceNameDate
            SourceNameDate = SourceName & SourceDate
            'Run the search for the new SND
            DestReturn = coll.indexof(SourceNameDate, 0)
            If DestReturn = 0 Then
                DestReturn = 1
            End If
            'If new SND is not found in collection then
            If DestReturn = -1 Then
                'Find empty row again
                DestLRow = Destws.Cells(Destws.Rows.Count, "A").End(xlUp).Row + 1
                'Enter employee data again for next date
                Destws.Range("A" & DestLRow).Value = SourceName
                Destws.Range("B" & DestLRow).Value = SourceEmpNum
                Destws.Range("C" & DestLRow).Value = SourceDate
                'Set 2nd time range
                Set ExtraAddRng = Destws.Range(Destws.Cells(DestLRow, 4), Destws.Cells(DestLRow, EndTime + 4))
                'For each cell in second time range put labour account
                For Each ExtraAddTime In ExtraAddRng
                    ExtraAddTime.Value = SourceAcct
                Next ExtraAddTime
                'Add new SND to collection
                coll.Add SourceNameDate
                'Run the search for the new SND again
                DestReturn = coll.indexof(SourceNameDate, 0)
                If DestReturn = 0 Then
                    DestReturn = 1
                End If
            Else
                'Set 2nd time range
                Set ExtraAddRng = Destws.Range(Destws.Cells(DestReturn + 1, 4), Destws.Cells(DestReturn + 1, EndTime + 4))
                'For each cell in second time range put labour account
                For Each ExtraAddTime In ExtraAddRng
                    ExtraAddTime.Value = SourceAcct
                Next ExtraAddTime
            End If
        Else
            Set AddRng = Destws.Range(Destws.Cells(DestReturn + 1, StartTime + 4), Destws.Cells(DestReturn + 1, EndTime + 4))                                                                                                     
            For Each AddTime In AddRng
                AddTime.Value = SourceAcct
            Next AddTime
        End If
    End If
   
NextSourceRng:
Next SourceRng

End Sub
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That would work but I want to be able to see by employee by minute.
You can change the grouped rows step and add "employee" in the group by.
 

Felix1980

New Member
Joined
May 16, 2018
Messages
37
Hello GraH, Is there a way to have a pivot table show the Account instead of a count of the account? I added the employee column, but now when I go to look at the pivot, I would like to add the Account they were working in at the time, however it only lets me choose count, etc. Also, the time is strangely showing across the top like : 1am, 1pm, 1:01am, 1:01pm....

The VBA shows like this :

1609188579953.png
 

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,678
Members
414,009
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top