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

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.

@Ashrita

Welcome to forums

You can try as

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

Hope this may helps you

Thanks

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

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.

Video explanation :

Regards
Balamurugan.S

1 Like

I tried to use this package , but the below error occured. Please help me, Thanks in advance.Error UiPath Studio Pro Community

1 Like

Can you please share a sample workflow please?

Thank you
Balamurugan.S

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

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”

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

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

image

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 :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

2 Likes

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”

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