How can I change the different date format of a datatable to one specific date format

Hi everyone,
I need a help. I have extracted order date from pdf invoices. Date format is different for some invoices. But I need to make it all in one specific date format. I want to change it before I write to excel file. How can I do that?
My date format should be “yyyy-MM-dd”

image

Regards,
Ekram

Hi @emshihab,

Once you’ve scraped the item, replace any instances of . and - with /, and then the following expression:

DateTimeVar = DateTime.Parse(YourVar,System.Globalization.Cultureinfo.InvariantCulture)

This should take your string and assign it to a datetime variable, and then you can write the DateTime in your desired format with:

DateTimeVar.ToString(“dd/MM/yyyy”)

Hi @william.coulson,
I tried your solution. But it shows an error.
Assign activity:
dataset2.Tables(0).Rows(0).Item(“Order date”) = DateTime.Parse(dataset2.Tables(0).Rows(0).Item(“Order date”),System.Globalization.Cultureinfo.InvariantCulture)

image

Hey @emshihab,

What’s the full error?

WIthout seeing the full error, I would guess you’re assigning the DateTime item to an Object? Try making a new DateTime and doing these assigns:

DateTimeVar = DateTime.Parse(dataset2.Tables(0).Rows(0).Item("Order date"),System.Globalization.Cultureinfo.InvariantCulture)

dataset2.Tables(0).Rows(0).Item("Order date") = DateTimeVar.ToString("dd/MM/yyyy")

Hi @william.coulson,
I have corrected my assign activity based on your suggestion. Still it shows an error.

The Object from the DataTable is still an object, try:
DateTime.Parse(dataset2.Tables(0).Rows(0).Item(“Order date”).ToString,System.Globalization.Cultureinfo.InvariantCulture)

I tried this one. It shows a different error.

Should I have to change the type of assign variable (dtv2)

image

dtv2 needs to be DateTime, not String

This time. It worked. But the problem with wrong date format (Format is dd.MM.yyyy. But considers MM.dd.yyyy) because I have two different types of date. How can i solve this issue?

after modification should be
4/12/2021 MM/dd/yyyy 2021-04-12
8/10/2021 MM/dd/yyyy 2021-08-10
06.10.2021 dd.MM.yyyy 2021-06-10 wrong 2021 - 10 -06
8/10/2021 MM/dd/yyyy 2021-08-10

I am trying to think of how to distinguish dd/mm from mm/dd… Do the dates on your invoices come from specific suppliers? If so, do they have a consistent invoice format that will always use the same date format? You could have an If statement to say if the supplier is “Supplier A” or “Supplier B” then the date format is dd/mm, and if it’s any other supplier then it’s mm/dd. Within the If statement, you could introduce a specific culture like so:

If the date format is dd/mm: dtv2 = DateTime.Parse(YourVar, new Globalization.CultureInfo("en-GB"))

If the date format is mm/dd: dtv2 = DateTime.Parse(YourVar, new Globalization.CultureInfo("en-US"))

And then when you write it to your desired format, it should be correct.

Your suggestion sounds great. Because the date format for each supplier is same.
I have changed a bit. I have used DateTime.ParseExact to mention data format (how it is written in the pdf invoice) in stead of “en-GB”/“en-US”

Regards,
Ekram

1 Like

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