top of page

Employee Attendance Mass Export

Sayonara, human errors. Automation through VBA, streamlining recordkeeping for 500+ employees. 

vba_scqhoe.jpg

Problem: Employee attendance accruals (sick days, personal days, etc.) were kept manually, resulting in data entry riddled with human error and a large investment of time. Employees' check stubs listed accrual totals one month behind, causing frequent questions/confusion.

Tool(s) Used: 

  • Excel VBA

  • VLOOKUP functions

  • IF functions


Solution: Excel VBA button to clean mass amounts of data into an import template in accordance to our system of record keeping, Tyler Technologies' MUNIS Enterprise ERP Software.

​

Optional File to Follow Along:

Attendance Spreadsheet

Screenshot 2024-08-13 124021.png

* Sensitive data scrubbed for portfolio purposes.

​

Functionality:

  • A similar version of this spreadsheet (without any Excel macros) was distributed to the attendance clerk/secretary at each district school.
     

  • Attendance is kept in real-time and updated daily. 
     

  • Before payroll runs, the schools' data sources are aggregated into this .xslm file. 
     

  • With one press of a button all of the attendance data is exported into a new sheet ("Export to MUNIS") which is formatted into the import-friendly template ("Copy-Paste This Sheet To Munis")

Data Aggregation

At a specific time interval (in our case, once per month, due to a monthly pay basis), data is compiled into this master sheet from the individual school districts. At our size, the easiest method for this is copy/pasting the sheets into one, but data aggregation tools could be used for districts where this is not practical.

Once the data is aggregated into this sheet, pressing the button will run the following code:

Excel VBA Button Code

Cleansed Data

After the code has been run, the "Export to MUNIS" spreadsheet will have the clean, aggregated data.

Screenshot 2024-08-19 134840.png

From here, the data is manipulated with VLOOKUP, IF, and aggregation functions to prepare the data for .csv import: 

Screenshot 2024-08-19 135807.png

Results

This data is now prepared for import into our record keeping system, MUNIS. Prior to creating the infrastructure for this import, each individual line of data had to be manually entered. ​

With this import, the following quantitative improvements have been made to our work flow: 

  • 10+ hours of data entry streamlined, saving time in the work week.
     

  • Real-time depiction of attendance-accrual remaining balances.
     

  • 97 inaccuracies uncovered and corrected.
     

  • Moving forward, 100% accurate and clean data in our record keeping software, MUNIS. 

bottom of page