How to change date format to writing datatable

[sharad.bailkar]

I have excel file
Which have column includes dates and it’s type is also Date in Excel file in dd-MM-yyyy format
But after I read, it gives me some rows are System.Datetime like till date 1 to 12

And after that it gives system.string

My System Datetime format is
MM/dd/yyyy hh:mm:ss

But after I write the datatable it changes month as day and day as month till 12th date

I want dd/MM/yyyy format for all rows

But it fails
Can somebody help me to sort out this

Thanks in advance!

[SorenB]

Hello @sharad.bailkar

The Date format from the Excel sheet should contain the info that you need.
It is simply a matter of defining the format you desire.

DateVariable.ToString("dd/MM/yyyy")

If you want the display to change within Excel, it could relate to display settings there.

Regards
Soren

[Sanjay_Bhat]

Hi @sharad.bailkar

If you want to change the format of the column through UiPath
you can make use of format cells activity and pass the range (By getting the range through last Row index and the find value activities)

Hope this helps!!

[rajesh.rane]

@sharad.bailkar Please look at below images

1. my data Table
   
   

Date Format : MM/dd/yyyy hh:mm:ss

2.My LINQ
(From row In dt_Input
Let a=DateTime.ParseExact(row(“Date”).ToString,“MM/dd/yyyy hh:mm:ss”,System.Globalization.CultureInfo.InvariantCulture)
Select dt_Input.Clone.Rows.Add(a.ToString(“dd/MM/yyyy”))
).copytodatatable

Date Format : dd/MM/yyyy

3. My Output
   
   

Regards,
Rajesh Rane

[Pavan_Manal]

You need to normalize the values after reading.
Use Assign or For Each
For Each row In dt.AsEnumerable()
row(“DateColumn”) = DateTime.Parse(row(“DateColumn”).ToString.Trim,
System.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”)