Date_Format_Issue

Hello guys,
I have an excel reading the date column.
Code:-

image

Excel :-

image

Issue:-

RPA reading service column correctly (21/01/2021) only when the excel cell format is text.

if the excel cell format is date RPA is reading as (01/21/2021), this is wrong.

The excel contain random cell format.

so is there anyway to read the service column in a proper format.

I have used preserve format and modern read range activity it is not working.

@Yoichi

please help me out on this please

@Gokul_Murali

Try the below code in that message box

Row("Service Date").ToString("dd/MM/yyyy")

@aanandsanraj

@aanandsanraj

I have tried this code also : CDate(Row(“Service Date”)).ToString(“dd/MM/yyyy”).

Here if the cell format is string and if use this code RPA is throwing system exception.

@Gokul_Murali
What is your requirement? Why are you again converting to CDate?

@aanandsanraj

Here the excel cell format is text.
This expression only working when the cell format is date.

@aanandsanraj

See my requirement is rpa should pick the date irrespective of cell format.

@Gokul_Murali

UiPath reads the Excel date column in datetime format. If you wish to display it in a custom format, you need to convert it into a string using the following approach. Please note that you should not enable the “PreserveFormat” property option in the “Read Range” activity.

To convert the Excel date into a custom string format, you can utilize the ToString method in UiPath. Here’s an example:

CDate(Row("Service Date")).ToString("dd/MM/yyyy")

@aanandsanraj

Iam getting an error from this expression

@Gokul_Murali Do you have any idea to run macros.
Before reading file Run macro for specific column and make same format for all cells.
Then read the excel file.

Do you have any workflow regarding this

Hi @Gokul_Murali
Try this
Convert.ToDateTime(CurrentRow(“Service Date”).ToString).Date.ToString(“dd/MM/yyyy”)

I hope it helps!!

@Gokul_Murali let me check please share excel file.

@lrtetala

This expression will not take if the cell format is text,

this expression only work for the cell with date format.

@Gokul_Murali Can you share that Excel?

@aanandsanraj

DATE FORMAT EXCEL.zip (6.3 KB)

@Gokul_Murali

Check this source
DATE FORMAT EXCEL.zip (9.7 KB)

I just used a normal process flow i will move this to the REFRAMEWORK flow, and let you know if there is any error.

@aanandsanraj
Iam getting an error like this when i try to move this to reframework