Incorrect date format

Help Studio
,
1

Hello!
There was a problem with the date.
The robot compares one excel file (something like a pivot table) with other excel files and replaces the data in other excel files in accordance with the pivot table.
When it replaces a cell, it adds the word “updated”. On the left is the pivot table, on the right is what it updates. But instead of the format “16.03.2020 update”, it replaces with “03/16/2020 00:00:00 update”.
Here is my expression:
dtResult.Rows (intindex) .Item (“Date of making the registration record in the USRN”) = line.item (“Date of making the record of registration in the USRN”). ToString + " " + “update”
How to make a correct date format? (“16.03.2020 update”)

tsgyytt
tsgyytt1524×235 22.8 KB

2

Hi @sereganator,

This could be due to your excel application setting. Once you open the excel it displays the date format you have in your excel.

What you can do is try to manually add the date into your excel, if it change from 16.03.2020 into 03/16/2020 and that means that you have to change your excel setting for the column/row.

I prefer to use .csv format insteam of .xls or .xlsx to avoid formatting issue in excel. I hope this helps.

3

I think the data type in the excel cells is okay, because if I don’t add the word “update”, the date format is correct.

4

Can you try something like this:

dtResult.Rows (intindex).Item(“Date of making the registration record in the USRN”) = Datetime.ParseExact(line.item(“Date of making the record of registration in the USRN”).ToString,“dd.MM.yyyy”,system.globalization.cultureinfo.invariantculture).ToString + " " + “update”

1 Like
5

Error: String was not recognized as a valid DateTime. Whats wrong?

6

How about
line.item (“Date of making the record of registration in the USRN”).ToString(“dd.MM.yyyy”) + " update"

7

Error: Option Strict On" does not allow implicit conversions from String to integer UiPath.

8

How about this:

image
image914×549 14.4 KB

Result:
image

9

Or are you reading the value as a string? If so then ignore my comment above. I mean you already have the input as a type of string and does’nt need to convert to string. What I’d like to know is if you try to print out your string right before you write to excel. What is your result?

10

How about

tempStr = date read from excel or 16.03.2020
tempStr = tempStr.Substring(3,2) + “/” + tempStr.Substring(0,2) + “/” + tempStr.Substring(6,4)
tempStr = Convert.ToDateTime(tempStr).ToString(“dd.MM.yyyy”) + " update"

11

Before writing to excel it reads like this: 03/16/2020 00:00:00. Although in the cell 16.03.2020

12

Ok, could you try either @Toppy’s or my solution above if it works, and try to print out a long the way.

13
  1. 03/16/2020 00:00:00
  2. 16.03.2020
  3. error Assign: String was not recognized as a valid DateTime.
14

  1. tempStr = “03/16/2020 00:00:00”

  2. tempStr = tempStr.Substring(0,2) + “/” + tempStr.Substring(3,2) + “/” tempStr.Substring(6,4)
    Result = “03/16/2020”
    (Since date format from 1. is MM/dd/YYYY HH:mm:ss so my previous comment date format is dd/MM/yyyy. That is why you received the error for invalid dateTime)

  3. tempStr = Convert.ToDateTime(tempStr).ToString(“dd.MM.yyyy”) + " update"
    Result = “16.03.2020 update”

hope this help.

1 Like
15

Thank you very much!

16

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.