Updating the IDOL Export File (new, June 2025)

On July 1, 2025, the Illinois Department of Labor (IDOL) will be releasing changes to their Certified Transcript of Payroll Portal, including updates to the Excel upload file that is compatible with their portal.

The LCPtracker team is actively working with IDOL to understand the implementation requirements and develop appropriate solutions for both LCPtracker and LCPcertified. At this stage our team has determined that there will need to be some considerable updates across multiple system components

We appreciate your patience as we work to build the updated IDOL requirements.

What to Expect

On July 1, 2025 the current Excel export file that can be downloaded from LCPtracker and LCPcertified will no longer be able to be uploaded into IDOL’s portal without some adjustments being manually made to the file.

All of the following columns will need to be added to the Excel file, regardless of whether you will be sending data within those fields or not.  All columns and headers on the Excel file must be a match to their newly issued format.

Directions for Updates

At this time, you will need to manually add six columns to the Excel file prior to uploading it into IDOL’s Certified Transcript of Payroll Portal.

You can add in the columns individually, OR follow the instructions below to use an Excel Macro to automate adding the columns in. 

Steps to follow:

• Enter and certify payoll normally in LCPtracker or LCPcertified
• Download the IDOL Excel template as you normally would from LCPtracker or LCPcertified
• Open the Excel file
• Add in the following six (6) columns in the proper order (either manually or using directions following to use an Excel macro)
• Fill in data to these six (6) new columns, as needed
• Re-save the file again in Excel format
• Upload to the IDOL portal

Adding in the New Columns

*Important* The header added to Row 1 for each column must be an exact match to what is listed below

Locating Information on IL Wage Determinations

The information to populate columns AU, AV & AW, can be found on the appropriate IL State Wage Determination for your project, based on the location of work (county).

Here is the link to the current wage determinations/prevailing wage rates.

Column AU for “GeographicDivision” will be populated by what is listed on the Prevailing Wages in the “Rg” column, as shown below.  The options that IDOL will accept are “ALL, N, NE, E, SE, S, SW, W, NW.”

Column AV for “ClassType” will be populated by what is listed on the Prevailing Wages in the “Type” column, as shown below.  The options that IDOL will accept are “ALL, BLD, FLT, HWY, O&C, RIV.”

Column AW for “ClassCode” will be populated by what is listed on the Prevailing Wages in the “C” column, as shown below.  The options that IDOL will accept are “1-14” or leave blank if it is not applicable.

Columns CP, CQ, CR are for additional benefit amounts paid to a worker and may not be applicable to all Contractors.  If you are not reporting any of these amounts, the columns still need to be added to the spreadsheet, however the values can just remain empty.

Additional Adjustments Needed (If Applicable)

Adjustments may be needed in the Excel file for any Overtime and Doubletime Hourly Rates entered into LCPtracker or LCPcertified, if the values entered into the system include the additional Fringe Benefit amounts paid only on OT or DT hours.

Currently, these amounts need to be reported as part of the OT or DT rates in LCPtracker or LCPcertified until new fields can be built to collect those individual values.

However, IDOL is requiring that these amounts be broken out so they will have to be separated manually for the time being.

Using the example below:

Base Hourly Rate without fringe benefits is $40.41

Overtime Hourly without fringe benefits is $60.615

Doubletime Hourly without fringe benefits is $80.82

Additional benefit amounts for OT hours worked only is $3.11

Additional benefit amounts for DT hours worked only is $6.21

In LCPtracker & LCPcertified, enter the SUM of the hourly rate and the additional fringe benefit for OT or DT hours.

In the Excel file that will be uploaded to IDOL, the values need to be broken out as follows:

• Column CF for “PrevailingOTWage” is $60.615
• Column CV for “PrevailingDoubleTimeWageRate” is $80.82
• Column CQ for “AddOT15” is $3.11
• Column CR for “AddOT20” is $6.21

Record a Macro to Insert Six (6) New Lines to IDOL Export

Once you have generated the ILDOL Export and saved to the correct Excel (.xlsx) file format, you will need to first show the Developer tab on your Excel Workbook file which is hidden by default.  To enable this, you will need to first complete the following steps.  

Click on File > Options in the lower left corner  

Within the Options library, select Customize Ribbon and then select Main Tabs from the “Choose commands from:” dropdown

Select Developer, click Add >>, and then click OK to add it to your Customized Ribbon

After adding the Developer tab, you are ready to create the Macro to add your additional columns.

1. Recording the Macro

   elect the Developer tab

   1. Click Visual Basic
      
   2. Select Insert and click Module
      

   
   

   A blank page will open for you to copy the code on the following page: (copy full script until End Sub)

   
   

   Code to copy

   
   

   Sub AddCustomColumns()

   Dim ws As Worksheet

   Dim lastCol As Long

   Dim i As Long

   Dim colIndex As Long

   Set ws = ActiveSheet

   ' Find the column with header "WorkClassification"

   For i = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

   If ws.Cells(1, i).Value = "WorkClassification" Then

   colIndex = i + 1

   Exit For

   End If

   Next i

   If colIndex > 0 Then

   ws.Columns(colIndex).Insert Shift:=xlToRight

   ws.Cells(1, colIndex).Value = "GeographicDivision"

   ws.Columns(colIndex + 1).Insert Shift:=xlToRight

   ws.Cells(1, colIndex + 1).Value = "ClassType"

   ws.Columns(colIndex + 2).Insert Shift:=xlToRight

   ws.Cells(1, colIndex + 2).Value = "ClassCode"

   End If

   ' Refresh column count

   lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

   ' Find the column with header "HourlyTrainingAccrued"

   For i = 1 To lastCol

   If ws.Cells(1, i).Value = "HourlyTrainingAccrued" Then

   colIndex = i + 1

   Exit For

   End If

   Next i

   If colIndex > 0 Then

   ws.Columns(colIndex).Insert Shift:=xlToRight

   ws.Cells(1, colIndex).Value = "HourlyOtherInsurance"

   ws.Columns(colIndex + 1).Insert Shift:=xlToRight

   ws.Cells(1, colIndex + 1).Value = "AddOT15"

   ws.Columns(colIndex + 2).Insert Shift:=xlToRight

   ws.Cells(1, colIndex + 2).Value = "AddOT20"

   End If

   MsgBox "Columns have been added successfully.", vbInformation

   End Sub

   
   

   The below view will display once the script is pasted in the box:

   

   
   

   Close the Module and Visual Basic and proceed to running the Macro.

   
   

   Running the Macro to Add the Additional Six Columns

   1. Click Macros
      
      
   2. The code you created will show and you will click Run
      
      
   3. This will create the additional columns in the correct order and format needed to upload to the IDOL.   
   4. You will receive the following message once completed.  
      

   
   

   Note: after the macro is completed, you will need to add the required information to columns AU (GeographicDivision) and AV (ClassType).  

   
   

   If applicable, you will also need to make changes to the fields within columns AW, CP, CQ, and CR as well as any other manual adjustments needed.