Read Date from Excel into DateTime

Help Studio
, ,
1

Hi guys,
I need your help here please.
I have an excel table with date entries in the form 03.08.2020 16:22.
This is the German format (dd:MM:yyyy hh:mm)
In excel I formatted the field into user-defined as TT.MM.JJJJ hh:mm which is the same as above in German.

The problem is that UIPath switches date and month, which leads to errors like this one:
Get Row Item of the column Process Start Date: 16.09.2020 08:00 is not a valid value for DateTime.
Does anyone know how I can solve this?

Thank you in advance!

2

Hi, welcome to the community!
Are you using this column to write as text somewhere or you need it as a date object?

3

@Jarvis1

Welcome to forums

Check below post

Thanks

4

Thank you very much!
That already helped, now I know how to get the date from the excel table without getting an error.
However, my plan was to convert the string into DateTime so that I can check whether the date is lower than today.
To get the entry of the table I used assign (1):
deliveryDateStr = row(“NON Committed Delivery Date”).ToString

Then I used another assign activity (2):
deliveryDateStr = convert.ToDateTime(deliveryDateStr).ToString(“dd.MM.yyyy hh:mm”)

After that I wanted to change the variable from String to DateTime (3):
deliveryDateDt = Convert.ToDateTime(deliveryDateStr)

The problem is that I still get an error for the second assign activity when I have a day that is bigger than 12. It is still the error “not a valid value for DateTime”

Do you know how to fix this?

Thank you very much!

5

@Jarvis1

Can you share your excel file with sample data

So that i can check

Thank you

6

It is actually a pretty big and complex table but I made a sample with the column I am talking about.
Thank you!
Test RPA.xlsx (15.8 KB)

7

@Jarvis1

I checked your file but there was no sample data of the column

image

Can you update with sample Dates in the exact format?

Thanks

8

Oh sorry! I just changed the format of the cell and forgot to enter the dates.
Here is the new file.
Test RPA.xlsx (15.8 KB)

9

Hi @Jarvis1

Try out the below workflow :-
MainPratik.xaml (13.5 KB)
Test RPA.xlsx (15.6 KB)

Hope this helps you

Mark as solution and like it if this helps you :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

10

Hey
Thank you very much for your effort!
Unfortunately, I still get an error:
Assign: Conversion from string “28.12.2020 18:00” to type ‘Date’ is not valid.

11

Hi @Jarvis1

Again i have updated the workflow :-

Main.xaml (16.8 KB)
Test RPA.xlsx (15.6 KB)

Hope this helps you :sweat_smile:

Mark as solution and like it if this helps you :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer: