Changing Column Type to Date Format

Help
1

I have an Excel workbook where the rows in all date columns appear as random numbers such as “43217”, “45672” etc.

I need to change the formatting of all these columns to dd/MM/yyyy format.

How can we achieve this ?

Thanks.

3

I am hoping your Excel sheet contains data in date time format in which case you could use parseExact method to convert it into datetime as per your needs … if your Excel value itself is not in datetjme format you would need to know what the value is … rather how to cast it in date time … and apply that format using parseexact …

4

Sheet 1 contains all data in the required date format.

When I copy the data from Sheet 1 to Sheet 2 using UiPath, the dates change to random numbers like “42713”.

Can you help me understand the parseExact method ?

Thanks!

5

Check this link Parse Date Activity if you are reading days from sheet1 rhen I presume you are using read rage activity … ourput of that is data table ? … you would need to parse the value in date column while you write the data table in sheet 2 … check above example and see if you can modify it according to your needs …

6

Hey @sai.kiran ! This didn’t help. Let me explain the problem -

Sheet 1 has say 25 columns and 3000 rows. Out of those, 6 columns are date columns. Now in Sheet 1, all date columns have date values in the required format (MM/dd/yyyy). I use a Read Range on Sheet 1, save it to a data table and then write it to Sheet 2. This is when the values in all those 6 columns in Sheet 2 change to random numbers like 42713, 42314 etc etc. What I understand is all these different values represent different dates.

The solution that you suggested doesn’t hold good here because the value that I pass to “FromOADate(value)” is not the only value in the column. I tried it using one value (42713) for one column and all values in the column got updated to “12/09/2016” (MM/dd/yyyy format). However, the actual dates in all rows of the column are different.

Do you have any workaround to solve this problem ?

Please help.

7

Hi @susbasu,

Use for each row activity to update the value in the column using assign activity.

Regards,
Arivu

8

I solved that kind of problem by exciting a marco at the end

9

Check this workflow, and excel attached ,

Main.xaml (11.5 KB)
Source Excel sheet.xlsx (8.7 KB)

You would need to loop though the rows which conatins date format , and use the parse exact to format the date as per its input.

Also check out the format of the cells where you are putting in the dates. If it still shows as number , maybe you might have explictly formatted them to be numbers ?? try to put the cells in general format and see if it works.

2 Likes
10

Maybe you can try this:

convert.ToDateTime(“01/01/1900”).AddDays(43217-1).ToString(“dd/MM/yyyy”)

For the datetime 01/01/1900 is equate to 1 in Excel.
Hope it helps.