Excel Date column/General column conversion to dd-mm-yyyy format

[Ashrita (AshritaReddy)]

Scenario: downloading an Excel Sheet from mail, here in the excel the Column is set in Date format/general(User sends in different formats, eg: 07/27/2020,27-07-2020). then when trying to read the excel, how to convert that to exact format as dd-mm-yyyy.

[Srini84 (Srinivas Kadamati)]

@Ashrita

Welcome to forums

You can try as

Convert.ToDateTime(YourDate).ToString(“dd-MM-yyyy”)

Hope this may helps you

Thanks

[Ashrita (AshritaReddy)]

yes of course im using that when the format is in general,ie(27-07-2020). but the date is in “07-27-2020” format,so throwing an error,as it is not in correct format

[balupad14 (Balamurugan)]

Hi @Ashrita,

Welcome to UiPath Community !

Here is an activity called “change cell type” . Using this activity that you can able to change cell type.

UiPathGo - BalaReva Excel Activities

Hi , In the release Version 2019.2.0. I have added a new activity called Change Cell Type. It changes the format of the value like below. [image] In the Cell Format , can specify the custom format what we have in the excel Like below image. [image] Sample : UiPathGoExcel.zip (18.4 KB) This is the video to configure the package in local. Here is the link to know about the formats. Regards Balamurugan.S

Video explanation :

UiPath | How to change the cell or column format in Excel | Guide (datatype)

Regards
Balamurugan.S

[Ashrita (AshritaReddy)]

I tried to use this package , but the below error occured. Please help me, Thanks in advance.

[balupad14 (Balamurugan)]

Can you please share a sample workflow please?

Thank you
Balamurugan.S

[Ashrita (AshritaReddy)]

It is showing me a message when im trying to upload my workflow. “Sorry new users cannot upload attachments”.

[Ashrita (AshritaReddy)]

when using the above Cell Format activity, in one scenario im getting error .ie: If the Cell is already in Date Format(21/07/2020) and now again if im trying to change the cell format to “dd.mm.yyyy” then it is being converted to “21.00.2020”

[balupad14 (Balamurugan)]

@Ashrita

I tried to use this package , but the below error occured. Please help me, Thanks in advance.

Check it that you have used this activity inside excel application scope. If yes. Don’t use inside excel application scope for the same file.

Thank you
Balamurugan.S

[Pratik_Wavhal]

Hi @Ashrita

when using the above Cell Format activity, in one scenario im getting error .ie: If the Cell is already in Date Format(21/07/2020) and now again if im trying to change the cell format to “dd.mm.yyyy” then it is being converted to “21.00.2020”

You have used the small “mm” for Month so it is giving you error and wrong output.

Use the Capital MM for the Month in the format
Like

"dd.MM.yyyy"

Then it will give you perfect output

mm is used for minutes thats why it is giving you 00 in the month place

Another one more thing you can also implement the If Condition as below if the Input Date Format within Excel is of different Format and you always want the output in “dd-MM-yyyy” format :-

For Format “MM/dd/yyyy” :-

DateTime.ParseExact(CDate(row("Input Dates")).ToString("MM/dd/yyyy"), "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”)

For Format “dd.MM.yyyy” :-

DateTime.ParseExact(CDate(row("Input Dates")).ToString("dd.MM.yyyy"), "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString(“dd-MM-yyyy”)

Workflow and Excel for the Same :-
MainPratik.xaml (13.7 KB)
New Microsoft Excel Worksheet.xlsx (9.1 KB)

Mark as solution and like it if this helps you

Happy Automation

Best Regards
Er Pratik Wavhal

[Ashrita (AshritaReddy)]

when using the above Cell Format activity, in one scenario im getting error .ie: If the Cell is already in Date Format(21/07/2020) and now again if im trying to change the cell format to “dd.mm.yyyy” then in excel it is changed as “21.07.2020”, but when im trying to read the excel and the cell value is shown as “21.00.2020”. the month is displayed “00”