How to convert date format from dd-mm-yyyy to MM-dd-yyyy in excel

[sathish_Kumar6 (sathish Kumar)]

For example i have date in excel as 10-05-2023 from this to 05-10-2023

[fernando_zuluaga]

Hi @sathish_Kumar6

Give a try with the following

DateTime.ParseExact(strInput, "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("MM-dd-yyyy")

Remember to replace the strInput variable for your currentRow variable

Regards

[sathish_Kumar6 (sathish Kumar)]

Im getting error

[adiijaiin (Aditya Jain)]

hi @sathish_Kumar6 ,

Can you provide the date that you’re getting in the sts variable in assign activity, Did you give the correct format there?

Or can you screenshot the date that you’re getting from Excel file?

The expression to convert data is written below and that should work if your input date format is coming as dd-MM-yyyy
DateTime.ParseExact(sts, “dd-MM-yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“MM-dd-yyyy”)

Thanks,
Aditya

[lrtetala (Lakshman Reddy)]

Hi @sathish_Kumar6

Try this

Regards,

[fernando_zuluaga]

Hi @sathish_Kumar6

Can I see the value you are retrieving in that variable please? I want to check the format

Regards

[sathish_Kumar6 (sathish Kumar)]

I have tried the given format but its not working so Iam Passing the variable as string so Is there any issue

[adiijaiin (Aditya Jain)]

Hi @sathish_Kumar6 : can you share your source file from where you’re reading the date column?

[sathish_Kumar6 (sathish Kumar)]

Sample.xlsx (10.0 KB)
Please check that sample file

[pratik.pandya]

@sathish_Kumar6

Use this Expression for changing Date Time Format.

DateTime.ParseExact(DateTime_Variable, “dd-mm-yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“MM-dd-yyyy”)

Regards

[Shubham_Kinge]

Try to use this one
datetime.Parse(CurrentRow(3).ToString).Tostring(“dd-MM-yy”)
or
DateTime.ParseExact(CurrentRow(3).ToString,“MM/dd/yyyy”,system.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”)

[adiijaiin (Aditya Jain)]

Hi @sathish_Kumar6 ,

for me the date is visible in this format from your sample file: i.e. dd/MM/yyyy

So the expression for this would be:
DateTime.ParseExact(DateTime_Variable, “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture).ToString(“MM-dd-yyyy”)

[supermanPunch (Arpan)]

Hi @sathish_Kumar6 ,

Could you check the below Post, it does address the same issue, you could just use the format that is required for your case :

DateTime.ParseExact the whole Column without looping

Hi @arthurang , If the Target Goal for you is to represent the dates in Excel Sheet Column in MM/dd/yyyy format, maybe you could use the Format Cells Activity if you have the latest versions in place. [image] You would require to know which column the Date column is present (A or B or C, in my case it is J), then we can use the Range as J:J. If we do not know the position of the column in the Excel sheet too, we can dynamically be able to find it as well. Input : [image] Output: [image] …

[postwick (Paul Ostwick)]

You do this in Excel by editing the cell’s formatting. It’s important to understand that datetimes don’t have a format. You format them when you view/output them. That’s what Excel is doing. It’s storing a datetime value and displaying it in a certain format - which you can change by right-clicking the cell (or column, or row, etc)

[fernando_zuluaga]

Hi @sathish_Kumar6

Just enable the preservet formar property in your read range activity, with this we will ensure that we are retrieving the same format we are watching in excel, then use the code i’ve shared

DateTime.ParseExact(strInput, "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("MM-dd-yyyy")

Regards!