← Back to home

Training Attendance Architecture

A USPS HR Training Department ran on paper logs and copy-paste chaos. This is the rebuild: a validated Excel + VBA intake, a Power Query master log, and a dashboard the Training Specialist actually uses.

40–50 hrsadmin time/month reclaimed
100%of logs previously hand-entered
400%capacity increase over original sheet
DMAICLean Six Sigma framework used

How data actually moves

Click the intake node to see what happens when a trainer hits Submit.
1. Intake Sheet Trainer enters roster + late/early modifiers, hits Submit
→
2. VBA on Submit Writes a per-employee CSV line, emails a confirmation
→
3. Watched Folder CSVs land here, one per class, named by dept + date
→
4. Power Query Combines every file in the folder into one table
→
5. Master Log Live Train_Log table, one row per class entry
→
6. Dashboard Assigned vs. attended, hours earned by topic
 intake_submit.vba — simulated output
Simulated: this page doesn't send real email or write real files. The mechanics (CSV structure, Power Query combine, the dashboard math) are real and demonstrated in the downloadable workbook →

The code behind it

The VBA below is a reconstruction — same mechanism as the original, generic placeholders in place of the real recipient and network path. The formulas are the real ones from the production workbook: structural table references only, nothing to strip.
Sub SubmitToTrainingSpecialist()

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Intake")
    Dim csvPath As String, dept As String, classDate As String
    Dim fileNum As Integer, r As Long, lastRow As Long

    dept = ws.Range("Department").Value
    classDate = Format(ws.Range("ClassDate").Value, "yyyymmdd")
    csvPath = "\\hrssc\training\intake\" & dept & "_" & classDate & ".csv"

    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    fileNum = FreeFile
    Open csvPath For Output As #fileNum
    Print #fileNum, "Name,EIN,Modifier,CreditHours"

    For r = 6 To lastRow
        If ws.Cells(r, "B").Value <> "" Then
            Print #fileNum, ws.Cells(r, "B").Value & "," & ws.Cells(r, "C").Value & "," & _
                RoundToQuarterHour(ws.Cells(r, "D").Value) & "," & ws.Range("CreditableHours").Value
        End If
    Next r
    Close #fileNum

    SendConfirmationEmail dept, classDate, lastRow - 5

End Sub

Function RoundToQuarterHour(hrs As Double) As Double
    RoundToQuarterHour = Application.WorksheetFunction.Round(hrs * 4, 0) / 4
End Function

Sub SendConfirmationEmail(dept As String, classDate As String, empCount As Long)
    Dim outApp As Object, outMail As Object
    Set outApp = CreateObject("Outlook.Application")
    Set outMail = outApp.CreateItem(0)

    With outMail
        .To = "training.specialist@usps.gov"
        .Subject = "Attendance logged: " & dept & " " & classDate
        .Body = empCount & " employees logged. CSV saved to intake folder."
        .Send
    End With
End Sub
' Attrition — Yes/No/tardy against hours available vs. earned
=IF(C2="Yes", 1, IF(C2="no", 0, IF(C2="tardy", SUM(J2/I2), "")))

' Department — parsed from "DEPT - TOPIC" course name
=LEFT(Train_Log[[#This Row],[COURSE NAME:]], FIND(" - ", Train_Log[[#This Row],[COURSE NAME:]]) - 1)

' Course Topic — same course name, right-hand side
=MID(Train_Log[[#This Row],[COURSE NAME:]], FIND(" - ", Train_Log[[#This Row],[COURSE NAME:]]), 13)

' Code — unique key: topic + start date + end date
=MID(E2, FIND(" - ", E2) + 3, LEN(E2)) & "_" & TEXT(F2, "yyyymmdd") & "_" & TEXT(H2, "yyyymmdd")

' Dashboard — single-employee lookup against the live table
=VLOOKUP(B4, Train_Log[], 3, FALSE)
=VLOOKUP($B$4, Train_Log[], 17, )

' thematrix — live per-employee row spill for whoever is in Dashboard!B4
=FILTER(Train_Log[], Train_Log[NAME] = Dashboard!B4, "not found")

Case study

DMAIC format — Define, Measure, Analyze, Improve, Control
Situation
The training department relied on trainer-submitted paper attendance logs. They were frequently inconsistent, slow to transcribe, and often missing EINs, course data, or how to handle attendance edge cases like a late arrival or early departure. Manual entry consumed 5–10 hours a week — time that came directly out of the Training Specialist's capacity to build curriculum, manage trainer quality, or run compliance reporting.
Role
Sole builder. Owned the process mapping, the tool design, and the rollout, using a Lean Six Sigma DMAIC frame from problem statement through sustainment.
Action
  • Built a digital tracker with preloaded employee IDs, dropdowns for trainers and course types, and auto-calculating time fields with quarter-hour-rounded late/early modifiers.
  • Wrote VBA to generate a per-employee CSV on submit and email a completion confirmation to the Training Specialist.
  • Built a Power-Query-powered Master Log that combines every CSV dropped in a watched folder into one live table, updating automatically.
  • Locked formula cells, standardized entry templates across trainers, and documented the process for onboarding.
  • Layered a dashboard on the Master Log: assigned vs. attended, credit hours earned by topic, per-employee lookup.
Result
Manual data entry eliminated for the Training Specialist, whose time shifted from transcription to curriculum design and trainer-quality management. The system is resilient to edge cases and staff turnover, and training data is now auditable, scalable, and modernized — a foundation the department can keep building on, not a one-off fix.
Excel (Advanced + VBA) Power Query Microsoft Forms & Power Automate (planning) Lean Six Sigma DMAIC Process Mapping & SOP Restoration KPI Design
⇩ Download the demo workbook
600 synthetic training records, real department/course structure, working formulas throughout — no real employee data. Enable macros to see the VBA; the tracked formulas work without them.