Date format difference when adding excel as test data in Katalon

Product Forums Katalon Studio
,
1

I’m new in Katalon, I’ve created an excel file with the following date/time format:
image

But when I add the excel as test data in Katalon, it’s showing different format:

image
image715×181 6.54 KB

The test failed because of mismatched date/time format
image

How can I make Katalon to have the same date/time format after adding as test data?

2

Katalon’s “Data-driven testing” feature does not respect what type of datetime format you specified for each cells in a .xlsx file. KS just ignores the datetime format defined in a *.xlsx file. KS applies a datetime format it considers appropriate as default.

You should do either of

  1. change your test so that it follows the datetime format that KS applied.

or

  1. Don’t let KS to read an Excel file. Let it read a CSV file instead. You want to export the Excel worksheet into a CSV file. You want to make sure the datetime value is formatted in the format you want, like this:
Name,Start Time ,Start Time ,Duration,,,
WOW01A01,2021-04-20 8:36:07,2021-04-20 11:27:47,,,,
...

and you want to configure Katalon to load the CSV file, not .xlsx file, as Data.

If you definitely want to use the datetime format defined in the cells of *.xlsx file, and you do not like CSV file, then you have to stop using the Katalon’s “Data-driven testing”. You can invent your own way of importing Excel data directly from *.xlsx file while respecting the datetime format of cells using Apache POI, though I would not recommend it, it’s just cumbersome. Too much efforts required for too little benefit.

3

We also use Excel for data that includes date, You can:

  1. Store the date as text in the spreadsheet in the format you want.

  2. We stored as date format which when read is the number of days since Jan 0, 1990, so convert to a date string.

  3. We also use datetime format which when read is as above with a decimal value representing fraction of a day. This we also convert, Please see https://www.excelcampus.com/functions/how-dates-work-in-excel/#:~:text=The%20dates%20in%20Excel%20are,1%2F27%2F2016). for details.