Extracting Data Dynamically from Excel Sheets in IBM RPA — and Handling Different Date Formats

By Mohamed Ramadan — Business Automation | Integration Consultant.
IBM RPA/BAW/ODM/ACE/MQ Specialist.

Working with Excel files is one of the most common use cases in IBM Robotic Process Automation (RPA). However, anyone who has automated Excel tasks knows that one persistent challenge is dealing with date fields. They may look simple, but Excel can represent dates in multiple formats — often causing automation scripts to fail or produce inconsistent results.

In this article, I’ll walk you through how to extract data from Excel sheets using IBM RPA, with a focus on handling different date formats in a clean and reliable way.

Additionally, we’ll explore how to design your automation so it can adapt to changing Excel layouts — allowing your bot to locate and extract the correct data dynamically, even when the position of the fields changes.

Step 1: Connect and Open the Excel File

For this example, we’ll use a simple Excel file that contains a date label and value — just enough to demonstrate how to extract and interpret date information.

Begin by opening IBM RPA Studio and creating a new .wal script.
From the Commands Palette, drag the Open Excel command into your workflow.
Browse to your Excel file, and assign a variable to the Excel Instance field — this variable will hold a live reference to the Excel workbook during execution. Let the variable be “excel_inst”

Step 2: Get the Entire Sheet Table

Next, use the Get Excel Table command to read the contents of the spreadsheet.

In the Input Parameters, specify the target Spreadsheet.
In our case, enable the “Use first spreadsheet” option so the bot automatically selects the first sheet in the workbook.
Set the command to retrieve the entire table of data.

Then, in the Output Parameters, define a table variable “data_table”— this variable will hold the extracted data for further processing within your automation flow.

The extracted data is now stored in a variable called “data_table”, which organizes the information by row and column indices — just like a standard table structure.

In the next section, we’ll explore how to extract data dynamically from this Excel table, allowing your bot to locate and retrieve the desired values even if the layout or column order changes.

Step 3: Find the Data Label Occurrence

Next, use the Find Table Cell Occurrence command to locate the target label within your Excel data. In our example, the label text we’re searching for is “Date:”.

In the Input Parameter Mapping section:

• Set the Data Table parameter to the output variable from the previous step (the one returned by Get Excel Table).
• In the Value field, specify the label you want to find — in this case, "Date:".
• Leave the remaining parameters at their default settings.

In the Output Mapping section:

• Add a variable to hold the found cell value (the label itself) “label_value”.
• Create additional variables to capture the row and column numbers where the label was located. (“label_row”, “label_col”)

.

This will allow your bot to dynamically identify the position of the “Date:” field in the spreadsheet, even if its location changes in future versions of the Excel file.

Step 4: Adjust Rows and Columns

Based on the position of the identified label and its corresponding value, we’ll use the Evaluate command to calculate the exact cell location of the desired data.

In this example, the value resides on the same row as the label but in the next column. To manage this, configure the Evaluate command as follows:

• In the Expression input parameter, add 1 to the column number returned by the Find Table Cell Occurrence command.
• In the Output Parameter, define a new variable that will store this adjusted column number.

This ensures that the bot dynamically determines the position of the data value relative to its label.

Important Note

When defining the output variable in the Evaluate command, pay close attention to its data type.
By default, IBM RPA creates new variables as Text type, which may cause unexpected behavior when performing numeric operations.

To avoid this issue, edit the variable’s data type and change it to Number.
This ensures the calculation (such as adding 1 to the column index) works correctly and prevents runtime errors in your automation.

Step 5: Extract the date value using row and column

Now that we have the exact row and column of the date cell, we can extract its value.
Use the Get Excel Value command to read the data from the identified position.

In the Input Mapping section:

• Enable the “Use first spreadsheet” option to target the correct sheet.
• Pass the row and column variables obtained from the previous steps.

In the Output Mapping, define a variable that will hold the date value extracted from the Excel file.

Now, let’s log the Extracted Date Value.

Converting Excel Date Serials to Readable Date Values

When you extract a date value from Excel, you may notice that it’s returned as a numeric serial rather than a formatted date string.
In our example, the date 20/10/2025 returned as 45950.

To convert this serial number into a proper date format, follow these steps:

1. Convert Serial Text to Number
• Use the Convert Text to Number command to ensure the extracted value is a true numeric type.
• Store the result in a variable (e.g., date_value_as_num).

2. Define the Zero Date
• Create a variable of type DateTime (e.g., base_date).
• Assign it the value 1899-12-30 00:00:00 — this is Excel’s zero date, from which all serial date values are calculated.
3. Add Value to DateTime
• Use the Add Value to DateTime command.
• In the Input Parameters, set:
• Date: your base_date variable
• Value: the converted dateSerial number date_value_as_num
• Value Type: leave as Days (default)
• In the Output Mapping, define a variable (e.g., date_value_as_datetime) to hold the resulting DateTime value.

This process translates Excel’s numeric serial into a properly formatted date, which you can then display, log, or store in your target system.

Now let’s log the date value after conversion to a date time.

And if you need the date only without time, you can use the get date from datetime command

Logging the date value without time.

The complete WAL script

Conclusion

Handling Excel data in IBM RPA is straightforward once you understand how Excel encodes its dates. You can make your bots resilient to different regional formats and avoid those unexpected date bugs that often appear in production.

If you’ve built something similar, share your tips in the comments.

Tags: #IBMRPA #Automation #ExcelIntegration #DateFormats #BusinessAutomation