Date conversion issue using uipath

Help Studio
, ,
1

I have a column in excel file(type=custom), having a date as 30/04/24
when read, it reads as 5 digit value- 45321
i want to convert it into dd/MM/yyyy hh:mm:ss format

tried datetime.parseexact…system.globalization
but this throws error
Can anyone please help

1 Like
2

Hi @shilpashree.mohanty

can you try with below syntax.

DateTime.ParseExact(StrDate, “dd/MM/yy”, CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy HH:mm:ss”)

Hope it helps!!!

3

doesnt work in this way

4

Hi @shilpashree.mohanty

Could you share your input file if you don’t have any confidential information.

5

its in VDI, wont be possible
here is a snap, while reading it comes as 5digit numeric value
image

6

In only first column do you want to change the format… @shilpashree.mohanty

7

i have read that column value, it reads as a 5digit numeric value
i want to store it in a datetime variable and later paste to another excel

8

@shilpashree.mohanty

Try this ,

Var1 =
DateTime.FromOADate(yourExcelDate).ToString(“dd/MM/yyyy HH:mm:ss”)

The above will give you the output as string type so use it in assign activity with a string type variable on the left side.

For DateTime variable use,
DateTimeVar=
DateTime.ParseExact(var1, “dd/MM/yyyy HH:mm:ss”, CultureInfo.InvariantCulture)

Hope this helps .

9

If you want to change the format for first column, check the below LINQ Expression,

→ Use Read range workbook activity to read the excel and store in a datatable called Input_dt.
→ Use the assign activity and write the belo LINQ expression,

- Assign -> Output_dt = (From row In Input_dt
                         Let DateCol = DateTime.ParseExact(row("BOOK_DATE").toString, "M/d/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy hh:mm:ss")
                         Let newRow = Input_dt.Clone().Rows.Add(row.ItemArray.Select(Function(column) If(column.Equals(row("BOOK_DATE")), DateCol, column)).ToArray())
                         Select newRow
                                 ).CopyToDataTable()

→ Then use the Write range workbook activity to write the Output_dt in the excel.

Hope it helps!!

10

no, i dont want to change the column format
i want to change the 5digit value format to dd/MM/yy hh:mm:ss

11

want to convert the 5digit value to a datetime variable

12

Do one thing when you are reading the excel by using the Read range workbook activity, open the properites and check the preserve format then it will read in the same format… @shilpashree.mohanty

Hope you understand!!

13

this is not allowed at client end

14

Then we can’t make the 5 digit code to date format… @shilpashree.mohanty

we can achieve it by using the Preserve format option.

15

Yep have modified my reply please check.

16

i had faced similar issue in past and had got the resolution
hoping someone else can help me

17

Can you try with preserve format while reading.

18

not allowed to do that

19

@shilpashree.mohanty ,

Try testing my solution. I have tested with multiple values as well.

20

May i know which activity you are trying to read excel.