Get Dynamic DateTime Format

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”)

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

1 Like

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

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.” :disappointed_relieved:

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

I so appreciate your help…

What is this language?

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

Regards
Gokul

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

image

1 Like

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

Okay @Rawan.md

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

1 Like

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

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

image

1 Like

@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”)

1 Like

@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

1 Like

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

1 Like

@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. :blush:

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