How to convert dd-MMM-yy to yyyy.MM.dd format?

[vanithavs]

I have a column it contains 18-Sept-22 (Date format in Excel) and i have to convert that date to 2022.09.18.
In my workflow am reading that excel and passing it in a for each row data table, am getting value as 4490.
So any one please help in this regards…

[Rahul_Unnikrishnan]

Hello @vanithavs

You can use Modify date activity.

How to Modify Date in UiPath? Modify Date activity explained with demo

Thanks

[Gokul_Jayakumar (Gokul Jai)]

@vanithavs
The date column may be in Text format, So you are facing that issue

Try this to convert your date format.

DateTime.ParseExact("18-Sep-22","dd-MMM-yy",System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy.MM.dd")

[Gokul001 (Gokul Balaji)]

Hi @vanithavs

Yo can try with Format Cells activity

Check out the Workflow

FilterDate.xaml (14.9 KB)

Output

Regards
Gokul

[AkshaySandhu (AkshaySingh Sandhu)]

Copy from excel to web site

Hi @laredo , just give a try on this, while reading the excel you might be using Read Range activity. Click on preserve format and then perform the rest actions. [image] Thanks

[vanithavs]

The date column is in date format in Excel, so that am getting value 4490 in for each row. that’s why am not able to convert to correct date format.

[Gokul_Jayakumar (Gokul Jai)]

While using the read range of the worksheet, kindly check the Properties of PreserveFormat and try again.

[anon88744563]

Hi,
Hi,
while reading the Datatable in Readrange Activity.
Enable “PreserveFormat” in Option in Property Panel.

So it will Get perserve text format, then u can use normally

use For Each Row in Data Table
inside the For each
Assign=>
Str1=(Cdate(currentRow(“ColumnName”).ToString)).Tostring(“yyyy.MM.dd”)

Regards,
Harivishnu

[ushu (Usha kiranmai)]

Hello, @vanithavs Check the below workflow

Example.zip (10.4 KB)

input

output

[ppr (Peter Preuss)]

@vanithavs

am reading that excel and passing it in a for each row data table, am getting value as 4490.

we assume that there was maybe a copy paste cut off from the number value. We do see more often this sceanrio when excel read range from Workbook (not the Excel Application Scope) was done and we reveived the number value for the dates. Maybe you can confirm this implementation detail (Weokbook vs. EAS)

In such case we do have the date as OADate value and can convert as following:

the value form the row column we can handle and parse into an int32 as following:

DateTime.FromOADate(CInt(yourRowVar(ColNameOrIndex).TosString().Trim())).ToString("yyyy.MM.dd")

For a further analysis we recommend to debug, explore the datatable within the different debug panels and try your prototypes within the immediate panel

Understanding the 6 Debugging Panels of UiPath in the easiest way possible!

One of the very important topic of UiPath in Debugging is understanding the different debugging panels and when to use which panel during debugging. In this video, we will understand all the 6 debugging panels of UiPath in the easiest way possible. Video Link: Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - YouTube

[Gokul001 (Gokul Balaji)]

@Gokul001

Check out the Workflow

FilterDate.xaml (14.9 KB)

Have you check this above XAML file @vanithavs

Regards
Gokul