Hi Team,

I have a column with a custom format ( date: yyyy/mm/dd ) as shown in the screenshot, when I try to read this column data using datatable and map row functions I see a numeric value, something like this 44215. I would need the exact value from excel ( 2022/05/23), please suggest



------------------------------
KARTHIK GUTTAPUDI
------------------------------

Hi Karthik,

You just need to use the Convert Date Time to Text command:


dateTimeToText --date "${my_date}" --usecustomformat  --customformat "yyyy/MM/dd" my_date_custom_format=value

${my_date} is the variable where you are storing the value read from Excel and selecting Use custom format, you can obtain a text variable with the desired format.

Hope it helps.

------------------------------
Yon García Nistal
Skechers Iberia
------------------------------

Hi Yon García Nistal

Thank you for looking into it, in my case the variable "my_date" is of String type, so I cannot use the Convert Date Time to Text command.
If I change the my_date type to Date then the Get Excel Value command won't work.

------------------------------
KARTHIK GUTTAPUDI
------------------------------

Original Message:
Sent: Fri May 06, 2022 04:17 AM
From: Yon García Nistal
Subject: How to read excel columns with custom format

Hi Karthik,

You just need to use the Convert Date Time to Text command:


dateTimeToText --date "${my_date}" --usecustomformat  --customformat "yyyy/MM/dd" my_date_custom_format=value

${my_date} is the variable where you are storing the value read from Excel and selecting Use custom format, you can obtain a text variable with the desired format.

Hope it helps.

------------------------------
Yon García Nistal
Skechers Iberia
------------------------------

Hi Karthik,

This number is how Excel treats dates internally and IBM RPA just gets this raw value. Each number represents the number of days passed since a base date (1899-12-30), so what you have to do is add the number you read from the cell to the base date, as in the example below:

setVar --name "${dataBaseExcel}" --value "1899-12-30 00:00:00"
convertStringToNumber --text "${strData}" intData=value
addToDateTime --date "${dataBaseExcel}" --value ${intData} --type "Days" data=value​

Hope this helps.

Regards,

------------------------------
Vinicius Marques
------------------------------

Hi Vinicius Marques

Thank you for taking time and explaining it, appreciate your help, tried the way your suggested and it works.

------------------------------
KARTHIK GUTTAPUDI
------------------------------

Original Message:
Sent: Fri May 06, 2022 08:31 AM
From: Vinicius Marques
Subject: How to read excel columns with custom format

Hi Karthik,

This number is how Excel treats dates internally and IBM RPA just gets this raw value. Each number represents the number of days passed since a base date (1899-12-30), so what you have to do is add the number you read from the cell to the base date, as in the example below:

setVar --name "${dataBaseExcel}" --value "1899-12-30 00:00:00"convertStringToNumber --text "${strData}" intData=valueaddToDateTime --date "${dataBaseExcel}" --value ${intData} --type "Days" data=value​

Hope this helps.

Regards,

------------------------------
Vinicius Marques
------------------------------

Hi KARTHIK GUTTAPUDI,

I had a similar issue with this also, my solution was to change the excel custom format to a text format, so when the bot retrieves the value it will get exact what you see in the excel.

But I am curious if someone have a solution for it that we dont need to change the excel format. I will keep track of this post. =)

------------------------------
Bruno Raphael Marques de Santana
------------------------------

Hi Bruno Raphael Marques de Santana

Thank you for looking into it, in my case I cannot change the cell format manually.

The solution provided by Vinicius Marques is working fine.

------------------------------
KARTHIK GUTTAPUDI
------------------------------

Original Message:
Sent: Fri May 06, 2022 02:58 AM
From: Bruno Raphael Marques de Santana
Subject: How to read excel columns with custom format

Hi KARTHIK GUTTAPUDI,

I had a similar issue with this also, my solution was to change the excel custom format to a text format, so when the bot retrieves the value it will get exact what you see in the excel.

But I am curious if someone have a solution for it that we dont need to change the excel format. I will keep track of this post. =)

------------------------------
Bruno Raphael Marques de Santana
------------------------------