Read "date" type in excel turns out "datetime" type


#1

dear all, here i met a problem: in an excel, i have a column with the date shows like"2017/5/10" and its data type in excel is “date”, but when i read the column with uipath and using write line to show it, it becomes another type:“05/10/2017 00:00:00”, but what i need is “2017/5/10” to make a judgement of the date, does anybody know why it became this and how to transfer the date?many thanks.


#2

Hey @xiang

Please find attached sample and let me know if you have any doubts or query on this.

xiang_sample.zip (15.4 KB)

Regards…!!
Aksh


#3

thanks a lot!!


#4

Hello, I have a similar problem.
I read an Excel into a DataTable.
This transforms the Date type column from “09.12.16” to “42713”.
How can I change that string, “42713” back to Date in UiPath ?


#5

what is Data type in excel?


#6

DateTime yourDate = DateTime.FromOADate(42713)
https://www.dotnetperls.com/fromoadate


#7

Hello,

I too have a similar problem.
I have an excel with Timespan value (100:20:00) 100hrs and 20 minutes. I read that particular cell with Generic data type. The output i receive is 4.166667. I tried changing the data type to Timespan, DateTime, double and string. But nothing is working out.

How to get the exact value from the excel?


#8

hi @Reshma,

Please refer my workflow in the attachment file.

Best Regards,
Hoang Anh.test_01.xaml (12.5 KB)


#9

This will work for you :slight_smile:

Regards…!!
Aksh


#10

Hi,

I have a similar problem with excel dates, however your solutions not work always for me :slight_smile:

The date format in spain is dd/MM/yyyy and Excel Activity convert it to Date, if the day is 12 or less it reads the date as MM/dd/yyyy (swaps month and day) but when is more than 12 it reads it properly :frowning:

I will request UIPath to allow read all as string because i have a lot of problems (not only with dates also with money values)


#11

Have you found the solution? I have same problem


#12

@chuertas009 its not only one type of problem, so… try to give your problem and we can try to help.

I have solved this problem in different ways the step to solve every problem is to understand how UIPath Works.

  1. Generic Value is a Wrapper of different types, so internally it actually converts the value to a datatyped one.
  2. If you read excel, you can ONLY read generic value data type so it will be converted to date.
  3. Excel store the value as a double so you always can write the double value and change the format latter.(OADate)
  4. When you read the data you may read a cell or a range.
    a) Read cell: just convert it to string using format options. (here you can include culture formats also)
    b) Read range: you would need to do some workarounds, in my case i prefer to use LINQ and work with list than datatables.

In any case if you have not culture formats problems you only need to convert it to string with some modifications for example: dateTime.ToString(dd/MM/yyyy)

https://msdn.microsoft.com/en-us/library/zdtaw1bw(v=vs.110).aspx