Date Value Conversion Issue

Help
1

Hi,

I know how to convert the date but i am not able to do it in UI Path. I am reading the date from Excel as row(“Date”) and i am trying to type the date using type in to activity like this : “01/01/17”. when converting i am getting error Invalid cast to double.

I am using generic type dtDate value to hold this conversion : convert.todatetime(row(date)) . Please help

1 Like
2

Hey @SKE

As per assumption convert.todatetime(string) takes string as an argument. and just cross varify the datatype you are getting from datatable or excel whatever source.

otherwise you can use convert.todatetime(row(date).tostring()).

or if i assumed it wrong then please explain with more description or screenshot kind of
Regards…!!
Aksh

1 Like
3

Thanks for the quick turn around . when i am assigning

generic dtdatevalue = convert.todatetime(row(date))

getting Invalid cast from double to datetime.

if i am using (row(date).tostring) the date value format is changing from “01/01/2017” to 42376.

any small sample please.

4

see the updated(edited) first post and let me know :slight_smile:

and with error and without error here is a sample.
Test.xaml (5.3 KB)

let me know in case of any doubt.

Regards…!!
Aksh

5

Here are the complete details , I am doing some minor mistake pls correct me :

Excel Source column : Date . Data type : Date

As i mentioned generic value to hold the data that is : dtDate.

i)now i am assigning dtdate = convert.todatetiime(row(date))

above line throws : Exception Found : Invalid cast from double to datetime

iI)now i am assigning dtdate = convert.todatetiime(row(date).Tostring)

above line throws : Exception Found : string was not recognized as valid datetime.

Instead rowdate if i use string value that is 11/11/17 then i can able to get the result. row(date) is causing the problem here any hint to solve this issue.

Thanks for your extented support

1 Like
6

Not sure what your row(date) value is, but have you tried below?

   1)  dtdate = convert.todatetime(row(date).Tostring("MM/dd/yy"))

    2) dtdate = DateTime.FromOADate(row(date).Tostring)
7

Row(Date) - Comes from My excel. Excel contains date column . I am just retrieving and converting it as you mentioned as below : dtdate as generic value :

Option 1 results following error : strict on disallow implicit converssion from string to integer

Option 2 results : strict on disallow implicit converssion from string to double.

here my main problem is row(date) which is nothing but xl date. Do i need to do any format or at Excel level please suggest.

8

Can you try this:

double dtvalue = double.Parse(row(Date));
DateTime dateInfo= DateTime.FromOADate(dtvalue );

9

double dtvalue = double.Parse(row(Date));

when using above syntax I am getting following error. I believe my row(“Date”) format is causing the issue.I tried multiple options , can you please provide simple sample which reads date from Excel .

10

Can you provide your excel sheet( remove other columns if confidential)

11

Sorry from office machine can not upload anything. But here is my format: Column named “Date” and I have formatted that cell category as Date and Type as “03/14/17” (“MM/DD/YY”).

Date
01/01/17

12

I have tried with Date column in excel and it is already returning a datetime and you can use this for further.

A demo may be will be helpful for you.

Sample.zip (6.9 KB)

Regards…!!
Aksh

13

Thanks Aksh

16

Hey @aksh1yadav ! Can you please let us know how to use this when we have multiple date columns in a sheet ? Say for example we have 10 date columns.

Thanks,
Susmit

17

Hello Aksh,

Would you have any sample to calculate the date diff from a date in (dd-MMM-yyyy) (11-Apr-2018) format from today.

Thanks!

18

Please see the file attached.

Excel_ValidateDates.xaml (42.2 KB)

You can extract any column consisting dates. Input that as DataTable, iterate to see if the date extracted is valid or in a serial format. Serial formats are converted via method invoked in the attached XAML. Give it a read, you will understand the flow fairly and easily. Hope it helps :slight_smile:

3 Likes