Date and month interchanged while reading from excel

Help Studio
1

Hi all,
I have an excel which i have to download from a website and then performance some calculation. While reading the date column from excel, the date read by the bot has interchanged date and month. For example, the date in the excel was “07-11-2022” Ie the date is 7th of October, 2022. But while i passed the same value in message box the date came out to be “11-07-2022” Ie 11th of july 2022. The date and month got interchanged. Please suggest how i can get the date correctly as in excel.

2

HI @Jayesh_Rathi1

You can try with Format cell activity or change cell type activity

Format Cell

https://docs.uipath.com/activities/docs/format-range-x

https://docs.uipath.com/studiox/docs/tutorial-formatting-cells

Change cell Type

Regards
Gokul

3

Hi @Jayesh_Rathi1

In excel scope change to read formatting. It will read date in same format as you see…other options are rawvalue and default

Cheers

4

Hi,

Another approach:

it’s because your data is recognized as DateTime type and UiPath outputs it as InvariantCulture (similar with en-us).
So can you try the following expression?

DateTime.Parse(yourVarable).ToString("dd-MM-yyyy")

Regards,

5

HI @Jayesh_Rathi1

How about this expression?

DateTime.ParseExact(CurrentRow("Column Name").ToString.Trim,"MM-dd-yyyy",System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy")

Note : dd-MM-yyyy -> Based on excel date fomat

Or

You can try to change the format of the column

  1. Excel process scope activity
  2. Use excel file activity
  3. Format cell activity

image

Regards
Gokul

6

HI @Jayesh_Rathi1

Go to → Activities Panel → Click on Filter → Select Show Modern.

image

Step 1 : Use Excel process scope followed by Use Excel file activities.

Step 2 : Use Format Cell Activity,

Check out the XAML file

FormatCellStudioX.xaml (8.5 KB)

Regards
Gokul

7

Hi @Jayesh_Rathi1

Method 1:

Method 2:

  • After Reading the Date column modify the date with the required format using the Below Expression

arrFormats = {“dd.MM.yyyy”,“MM-dd-yyyy”,“dd-MM-yyyy”,“MM/dd/yyyy hh:mm:ss”,“dd/MM/yyyy hh:mm:ss”}
You can add various formats in the arrFormats which is Array of string {“”,“”} with the comma as seperator

DateTime.ParseExact(Row("Date").ToString,arrFormats,System.Globalization.CultureInfo.InvariantCulture,System.Globalization.CultureInfo.DateTieStyles.None).ToString("dd-MM-yyyy")

Now you can you will get the date as per your format and use wherever you want

Regards
Sudharsan

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.