Creating excel file changes date format from DD/MM/YYYY to MM/DD/YYYY

Hi
I am trying to combine multiple CSV files into one excel file with each csv file having a separate tab in the new excel file. The file creation and combining the csv files is working fine but the date format changes form dd/mm/yyyy to mm/dd/yyyy. Can some one please help to fix this issue. i have tried using hotkeys but thats also not working.

StringToDateTime.xaml (5.5 KB)

This should help depending if you date is a string or datetime variable.

Let me know if it doesn’t. Its changing to the standard/default Uipath date time format

Thanks for the reply but this is not what i am looking for i have nine csv files and i am combining each one of the them to single excel file with a separate tab for each fie.

here is the single excel file with multiple tabs.

As you can see the date format is changed from dd/mm/yyyy to mm/dd/yyyy

here are the single files:

image

and here is how the date is shown in each csv file

Hi,

Check your system’s date format. It’s probably writing with your default system settings.

The date time format on the bot server is correct … The individual files are created correctly it changes when i write it to a single excel file.

image

Can you share you’re solution, or advise how you are writing the files back to excel?

i wont be able to share the whole Process but here is the part that is reading all the CSV files and generating the excel file.

I am looping through all the files and using read range.

this is the writing part using write range activity.

I can give you a temporary solution. Are you able to create that sheets which you write, before the process? You can use an excel template. So you can format that column manually before the process.

By the way, does it give the result you want when you print those files into separate excel files? Problem comes when you write them all in the same file, different sheets?

Sorry cant do any manual intervention.
I didn’t get the second bit of your message.
Problem is when i write it to same file. Individual files are fine the issue happens when i combine them to one excel file using write range activity. It changes the format of the date.

Hi

The problem is you’re output data table. Here is where it is converting the date into Uipaths default format. (mm/DD/YYYY). So when its writing back to you’re new file its changed before this point. If you were to use a write line and write you’re output DT string it will show in the changed format. Do you need to output the datable into string format? Can you let me see what you have in it?

Its difficult to offer a solution without understanding the logic but otherwise you could always read the new date time column and do a for each row to change the format back but that’s long winded and not the best option but it would work.

Hi Thanks for the information. i also thought that is the case but dont know how to stop it from converting. The datatable is created by reading the 8 csv files and then out putting them to a excel work book with each csv file as a separate sheet.
i am not sure how can i show you the datatable and also how can i stop it from converting to default UiPath date or is there a way of changing the default date of UiPath?

attached is the xaml for manipulating the CSV files and creating excel work book with each CSV file name as a separate sheet.

CSV_Main.xaml (19.3 KB)

Make sure all of you’re read ranges have preserve format ticked. And alternatively instead of using the filter wizard, you could use dt.Select method. I have tried to replicate you’re issues and these options have resolved the problem for me every time. But its proving difficult without seeing the whole solution and the reports. The only other solution I have is to loop through a for each and convert each row but it just doen’t seem like the right thing to do.

The xaml file i sent you earlier is basically the whole process. There are other work flows in the process, they are only to generate the csv files. I tried the preserve format but that copies the date as “######” into the excel file. I will try the datatable select method approach and see how it goes.
I am unable to share the files due to restriction and customer data involvement.

Thanks for your time and effort much appreciated. i will let you know if the datatable select method works