Date formation

[hanviprebday]

Hi all,
I have a excel with columns as below
Name Date Account
XYZ. 20/6/2022. 432
FGH. 20/6/2022. 123
ABC. 13-02-2023. 656
ASD. 13-02-2023. 546

So in Date column I want to keep the date as dd-MM-yyyy.

I am ready the excel, I am passing that dt_exceloutput to for each row, inside the foreach I have taken an assign
format date = Date time.ParseExtract(row(“Date”).To string.Substring(0,10),“dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”)

This is working fine until first 2 rows from 3rd row I am getting error as string was not recognised as a valid Date time.

can anyone help me to solve this issue

[Shiva_Nikhil]

@hanviprebday

format date = Date time.ParseExtract(row(“Date”).To string.Substring(0,10),{“dd/MM/yyyy”,“dd-MM-yyyy”},System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”)

you can add how many you want like using {“dd/MM/yyyy”,“dd-MM-yyyy”}

hope this helps

[Gokul_Jayakumar (Gokul Jai)]

Hello @hanviprebday
Try this

DateTime.ParseExact(YourVariable,{"dd/MM/yyyy","dd-MM-yyyy"},System.Globalization.CultureInfo.InvariantCulture,System.Globalization,DateTimeStyles.None).ToString("dd-MM-yyy")

[Parvathy (PS Parvathy)]

Hi @hanviprebday

format date = Date time.ParseExtract(row(“Date”).To string.Substring(0,10),{“dd/MM/yyyy”,"dd-MM-yyyy"}, System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”)

Hope it helps!!

[Gokul001 (Gokul Balaji)]

Hi @hanviprebday

Can you try with this beblwo expression

You can give the date format in the array

DateTime.ParseExact(row("Date").To string.Substring(0,10),{"MM/dd/yyyy","MM/dd/yyyy hh:mm:ss","dd.MM.yyyy"}, System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString(“dd-MM-yyyy”)

[hanviprebday]

Thank you all it’s working now

[kali]

For the future if u work with dates in EU format you can set your current culture and it’ll be easier.
as you can use, (convert to date) Cdate():

Norwegian culture for reference.
System.Globalization.CultureInfo.CurrentCulture =
new System.Globalization.CultureInfo(“nb-NO”)