Problems in DateTime

[CamiCat]

Hello friends,
Can I ask you a quick question?
After analyzing this Excel SvincoliManuali_1_20180521_preprocess.xlsx (8.6 KB)

with this xaml preprocessing.xaml (44.8 KB)
I got the following error:

Can you help me in resolving this problem?
Sorry for the big distrub.

Thank you
Camilla

[sara_s]

hi try with this

- YouTube

[balupad14 (Balamurugan)]

Hi @CamiCat,
The problem is datatime conversion between the source and conversion format .

File :
test.zip (1.5 KB)

Regards
Balamurugan

[CamiCat]

Thank you so much @sara_s.
I’ll try and let you know.
Camilla .

[CamiCat]

Thank you @balupad14.
I tried with your solution but it gives me this error.

Can you please help me again?
Thank you so much,
Camilla.

[balupad14 (Balamurugan)]

Hi @balupad14
can you send me the source with Main.Xml if possible

Already I have preprocess file which you sent it early…

Regards
Balamurugan

[CamiCat]

Thank you @balupad14.
I attach here the Main.
Main.xaml (8.9 KB)

[balupad14 (Balamurugan)]

Hi @CamiCat,
The date variable is having the value like below. It is month day and year.

you need to change the format like this

DateTime.ParseExact(data_effetto, “MM/dd/yyyy HH:mm:ss”,System.Globalization.CultureInfo.InvariantCulture)

I have changed the source for the testing . You can test it .

File : preprocessing.zip (4.2 KB)

Regards
Balamurugan

[balupad14 (Balamurugan)]

Hi @CamiCat,

I have noticed another one also. The column of the datatype is object . Y because it is defined as date format in the excel. See the pictures below.

Regards
Balamurugan

[CamiCat]

Thank you so much @balupad14.
In this case what should I do?
Thank you so much,
Camilla

[balupad14 (Balamurugan)]

Hi @CamiCat

you can proceed as specified in the above format.But the data should have the same date format. In this case you won’t worry about the date conversion.

Regards
Balamurugan

[CamiCat]

Hello @balupad14.
Sorry for the disturb.
SInce in the successive part of the workflow I should use the date in the format dd/mm/yyyy, how is it possible to read it in this way and not in the format mm/dd/yyyy?
Thank you so much,
Camilla.

[balupad14 (Balamurugan)]

Hi @CamiCat,
Give me some more minute . I will analyse it . and let you know.

Regards
Balamurugan

[CamiCat]

Thank you so much @balupad14.
I’ll wait for your answer.
Camilla

[balupad14 (Balamurugan)]

Hi @CamiCat,

I have created 2 type of format to match with your situation.

while you read in uipath it is like this.

File : DateFormat.zip (7.9 KB)

So Final thing is . Excel will have “dd-MM-yyyy”. but in he parse have to do it by reserve.

Regards
Balamurugan

[CamiCat]

Thank you so much for your precious help @balupad14.
I’ll try and let you know.
Camilla

[balupad14 (Balamurugan)]

Sure @CamiCat

Regards
Balamurugan

[Ignacio_Insua_Feito]

@CamiCat

SInce in the successive part of the workflow I should use the date in the format dd/mm/yyyy, how is it possible to read it in this way and not in the format mm/dd/yyyy?

I see the problem is already solved but i wanna reffer to another link where you can vote to improve this feature in UI_Path:

Excel activity - read cell display value

Hi, It would be nice to add a third activity to the Read Cell current duo (Read Cell and Read Cell Formula) - Read Cell Display Value (it’s available in the original Interop API as cell.Text). Reasoning: Excel likes to format values and store them in its internal representation, which might produce “weird” (at least at first glance) results when reading. Being able to read cell display value on columns storing f.e. date would give a WYSIWYG experience would “gotcha” moments in execution. Exa…

I will re-write my solutions because each time i face this problem i improve a bit my knowledge about date formatting.

The usuall problem is about culture info (language) in this case 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

For all of you that would need to work with strings and datetime remember that you can use .tostring(“dd/MM/yy HH:mm:ss”) notation to convert to string in different formats. And also you can use DateTime.parse(dateTimeAsStringInAnyCultureFormat, new system.globalization.CultureInfo(“Es-es”)) to read any different culture dates. de “ES-es” could be changed to other formats just look in then MSDN

I have been struggling with this problem again and a collegue recommend me to work with FormatValue activity that mostly resolves all this problems.

Finally for all of you that should use the parse solution al ot in the same workflow just remmber that you can set the system.globalization.cultureinfo.currentculture to new system.globalization.cultureinfo(“Es-es”) at the start of the workflow and will save your culture selection for all parses

[CamiCat]

Thank you so much @Ignacio_Insua_Feito.
I read your post carefully and try to get advantage from all of your suggestions.
I’ll try to et the system.globalization.cultureinfo.currentculture to new system.globalization.cultureinfo(“Es-es”) at the start and let you know what happens.
Thank you so much.
Camilla

[CamiCat]

Hello @Ignacio_Insua_Feito.
I used the activity “Parse date” but it didn’t work.

It doesn’t give an error message but it saves the date in the incorrect way.

Can you please help me again?
Thank you so much,
Camilla