Formula or VBA Employee Head Count by Minute By Account

Felix1980 · Dec 23, 2020

Hello, My problem is as follows : I have a data dump from our time keeping software which shows data like this :

Employee Name

Date

Employee #

In Punch

Labor Account

Org Job

Work Rule

Out Punch

Paycode

Start Time

Hours

Days

Money

Source

Comment

Note

Source

Name

Amount

Span

Shift

Daily

Cumulative

Employee 1

12/14/2020

10720

7:53AM

020/281/2810/001/02/9125/-

12:01PM

4:01

Employee 1

12/14/2020

10720

12:29PM

020/281/2810/001/02/9125/-

4:45PM

4:14

8:15

Employee 1

12/14/2020

10720

020/281/2810/001/02/9125/-

Out Punch

Late Out

0:15

Employee 1

12/15/2020

10720

7:55AM

020/281/2810/001/02/9125/-

12:02PM

4:02

12:17

Employee 1

12/15/2020

10720

12:29PM

020/281/2810/001/02/9125/-

5:20PM

4:48

8:50

17:05

Employee 1

12/15/2020

10720

020/281/2810/001/02/9125/-

Out Punch

Late Out

0:50

Employee 1

12/16/2020

10720

7:55AM

020/281/2810/001/02/9125/-

12:02PM

4:02

21:07

Employee 1

12/16/2020

10720

12:29PM

020/281/2810/001/02/9125/-

4:55PM

4:23

8:25

25:30

Employee 1

12/16/2020

10720

020/281/2810/001/02/9125/-

Out Punch

Late Out

0:25

Employee 1

12/17/2020

10720

7:56:00 AM

020/281/2810/001/02/9125/-

12:03:00 PM

4:03

29:33

Employee 1

12/17/2020

10720

12:28PM

020/281/2810/001/02/9125/-

5:01PM

4:28

8:31

34:01

Employee 1

12/17/2020

10720

020/281/2810/001/02/9125/-

Out Punch

Late Out

0:31

Employee 1

12/18/2020

10720

7:55AM

020/281/2810/001/02/9125/-

12:05PM

4:05

38:06

Employee 1

12/18/2020

10720

12:31PM

020/281/2810/001/02/9125/-

4:46PM

4:11

8:16

42:17

Employee 1

12/18/2020

10720

020/281/2810/001/02/9125/-

Out Punch

Late Out

0:16

Employee 2

12/14/2020

10605

5:25PM

020/218/2182/001/08/5142/-

5:28PM

Employee 2

12/14/2020

10605

5:28PM

/214/2140/001///

6:58PM

1:28

Employee 2

12/14/2020

10605

6:58PM

/218/2182/001///

9:17PM

2:19

3:47

Employee 2

12/14/2020

10605

9:44PM

020/218/2182/001/08/5142/-

9:51PM

0:04

3:51

Employee 2

12/14/2020

10605

9:51PM

/214/2142/001///

11:14PM

1:23

5:14

Employee 2

12/14/2020

10605

020/218/2182/001/08/5142/-

Out Punch

Early Out

2:46

Employee 2

12/15/2020

10605

5:24PM

020/218/2182/001/08/5142/-

5:25PM

5:14

Employee 2

12/15/2020

10605

5:25PM

/218/2182/001///

5:57PM

0:27

5:41

Employee 2

12/15/2020

10605

5:57PM

/214/2140/001///

6:32PM

0:35

6:16

Employee 2

12/15/2020

10605

6:32PM

/218/2182/001///

8:06PM

1:34

7:50

Employee 2

12/15/2020

10605

8:06PM

/214/2142/001///

9:47PM

1:41

4:17

9:31

Employee 2

12/15/2020

10605

020/218/2182/001/08/5142/-

Out Punch

Early Out

4:13

Employee 2

12/16/2020

10605

5:26PM

020/218/2182/001/08/5142/-

5:27PM

9:31

Employee 2

12/16/2020

10605

5:27PM

/218/2182/001///

9:32PM

4:02

13:33

Employee 2

12/16/2020

10605

9:56PM

020/218/2182/001/08/5142/-

11:22PM

1:20

5:22

14:53

Employee 2

12/16/2020

10605

020/218/2182/001/08/5142/-

Out Punch

Early Out

2:38

Employee 2

12/17/2020

10605

5:25PM

020/218/2182/001/08/5142/-

5:26PM

14:53

Employee 2

12/17/2020

10605

5:26PM

/218/2182/001///

10:23PM

4:53

19:46

Employee 2

12/17/2020

10605

020/218/2182/001/08/5142/-

Out Punch

Early Out

3:37

Employee 2

12/18/2020

10605

5:25PM

020/218/2182/001/08/5142/-

5:27PM

19:46

Employee 2

12/18/2020

10605

5:27PM

/218/2182/001///

5:28PM

19:46

Employee 2

12/18/2020

10605

5:28PM

/214/2140/001///

5:56PM

0:26

20:12

Employee 2

12/18/2020

10605

5:56PM

/218/2182/001///

7:19PM

1:23

21:35

Employee 2

12/18/2020

10605

7:19PM

/214/2140/001///

8:09PM

0:50

22:25

Employee 2

12/18/2020

10605

8:09PM

/218/2182/001///

9:35PM

1:26

23:51

Employee 2

12/18/2020

10605

10:04PM

/214/2142/001///

12:28AM

2:23

6:28

26:14

Employee 3

12/14/2020

11463

5:27AM

020/212/2120/001/03/4350/-

10:29AM

4:59

Employee 3

12/14/2020

11463

10:54AM

020/212/2120/001/03/4350/-

1:15PM

2:16

7:15

Employee 3

12/15/2020

11463

5:22AM

020/212/2120/001/03/4350/-

10:15AM

4:45

12:00

Employee 3

12/15/2020

11463

10:27AM

020/212/2120/001/03/4350/-

3:11PM

4:26

9:11

16:26

Employee 3

12/15/2020

11463

020/212/2120/001/03/4350/-

Out Punch

Late Out

1:11

Employee 3

12/16/2020

11463

5:25AM

020/212/2120/001/03/4350/-

10:34AM

5:04

21:30

Employee 3

12/16/2020

11463

10:57AM

020/212/2120/001/03/4350/-

1:51PM

2:47

7:51

24:17

Employee 3

12/17/2020

11463

5:22AM

020/212/2120/001/03/4350/-

10:52AM

5:22

29:39

Employee 3

12/17/2020

11463

11:13AM

020/212/2120/001/03/4350/-

1:41PM

2:19

7:41

31:58

Employee 3

12/17/2020

11463

020/212/2120/001/03/4350/-

Out Punch

Early Out

12:06

Employee 3

12/18/2020

11463

5:19AM

020/212/2120/001/03/4350/-

10:32AM

5:02

37:00

Employee 3

12/18/2020

11463

10:55AM

020/212/2120/001/03/4350/-

2:00PM

2:58

8:00

39: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.

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 :

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.

GraH · Dec 27, 2020

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

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 · Dec 27, 2020

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 · Dec 28, 2020

Felix1980 said:
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 · Dec 28, 2020

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 :

Formula or VBA Employee Head Count by Minute By Account

Felix1980

New Member

Excel Facts

GraH

Well-known Member

Felix1980

New Member

GraH

Well-known Member

Felix1980

New Member

Similar threads

Forum statistics

Share this page

Formula or VBA Employee Head Count by Minute By Account

Felix1980

New Member

Excel Facts

GraH

Well-known Member

Felix1980

New Member

GraH

Well-known Member

Felix1980

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock