Get Dynamic DateTime Format

[Rawan.md]

Hi there,

I have a specific workflow to get the date format from a column in an excel file >> the problem is I can’t know the date format some of these excel files will be in Arabic time like “2022-04-29 12:00:13 ص”, I tried a lot of formats but still give an error in recognizing the valid DateTime format… I want a way to find all “ص” OR “م” in the whole column and replace them with “AM” or “PM”…

But this is not the all problem because this excel file is different each time and will contain different DateTime formats !! like this “4/29/2022 1:00”

How to make get DateTime format dynamic so that whatever the excel file is contained in the date column will convert it to what I want !!!

that’s what I’m using to convert the given DateTime format to a specific one:

DateTime.ParseExact(posting_date, “MM/dd/yyyy h:mm”, System.Globalization.CultureInfo.InvariantCulture).ToString(“yyyy-MM-dd HH:mm:ss”)

[Gokul001 (Gokul Balaji)]

HI @Rawan.md

Check out this expression

DateTime.ParseExact(posting_date.Tostring,{"MMM dd, yyyy","MMM d, yyyy","MMM d, yyyy hh:mm tt","MMM dd, yyyy hh:mmtt"},System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("yyyy-MM-dd HH:mm:ss")

You can any date format based on needing here -> {"MMM dd, yyyy","MMM d, yyyy","MMM d, yyyy hh:mm tt","MMM dd, yyyy hh:mmtt"}

Regards
gokul

[Sudharsan_Ka]

HI @Rawan.md

Try this expresssion

DateTime.ParseExact(posting_date, {“MM/dd/yyyy h:mm”,"Other","Formats"}, System.Globalization.CultureInfo.InvariantCulture,System.Globalization.CultureInfo.DateTimeStyles.None).ToString(“yyyy-MM-dd HH:mm:ss”)

You can add what ever formats inside the curly braces {“”,“”,“”} like this

Regards
Sudharsan

[Rawan.md]

thank you so much for your quick reply @Sudharsan_Ka and @Gokul001

but non of these formats work with this DateTime ‘2022-04-29 12:00:13 ص’ !! I tried to add this format also “yyyy-MM-dd hh:mm:ss t” but still given the same error “Assign - posting date: String ‘2022-04-29 12:00:13 ص’ was not recognized as a valid DateTime.”

Do you have any idea to deal with this !!!

I so appreciate your help…

[Gokul001 (Gokul Balaji)]

@Rawan.md

ص’

What is this language?

You can try to remove the character and pass the Posting date into expression.

Regards
Gokul

[Gokul001 (Gokul Balaji)]

Use the regex expression and take only the date in the string and pass it in the expression @Rawan.md

System.Text.RegularExpressions.Regex.Match(YourString,"\d.{4}\d.{2}\d{2}\s\d.{2}\d.{2}\d{2}").Tostring

[Rawan.md]

it’s an Arabic language, ‘ص’ means AM, also “م” means PM !!

[Gokul001 (Gokul Balaji)]

Okay @Rawan.md

You can remove that using the regex expression and check out the above expression

[Gokul_Jayakumar (Gokul Jai)]

@Rawan.md

2022-04-29 12:00:13 ص

Hello @Rawan.md
You can use string replace expression to change the AM or PM from Arabic

YourString.Replace("ص","AM")

[Gokul_Jayakumar (Gokul Jai)]

@Rawan.md

م

@Rawan.md
Try this

DATEVAR=DateTime.ParseExact("2022-04-29 12:00:13 م","yyyy-MM-dd hh:mm:ss t",System.Globalization.CultureInfo.GetCultureInfo("ar"))

Where,
DATEVAR is variable type of syste.DateTime

PM

AM

You can convert it to desired string as DATEVAR.tostring(“yyyy-MM-dd”)

[Sudharsan_Ka]

@Rawan.md

Adding to @Gokul_Jayakumar above expression

If you need to use various formats Try this expresssion give your required format at the end of the expression inside .ToString(“Your required formats”)

DateTime.ParseExact("08-11-2022 00:00:00 ص",{"dd-MM-yyyy hh:mm:ss tt","dd.MM.yyyy hh:mm:ss"},System.Globalization.CultureInfo.GetCultureInfo("AR"),System.Globalization.DateTimeStyles.none).ToString("dd.MM.yyyy hh:mm:ss")

You can add what ever formats inside the curly braces {“”,“”,“”} like this

Regards
Sudharsan

[Gokul001 (Gokul Balaji)]

HI @Rawan.md

How about this expression

DateTime.ParseExact(posting_date.Tostring,{"dd-MM-yyyy hh:mm:ss tt","dd.MM.yyyy hh:mm:ss"},System.Globalization.CultureInfo.GetCultureInfo("AR-ar"),System.Globalization.DateTimeStyles.none).ToString("dd.MM.yyyy hh:mm:ss")

Regards
Gokul

[Rawan.md]

@Gokul001 @Gokul_Jayakumar @Sudharsan_Ka

I’m so appreciative of your fantastic help the problem has been solved as I want exactly what @Gokul_Jayakumar posted as the best solution for my scenario.

Sorry to take a long time to reply to you because I was trying all the solutions you mentioned.

thank you all.