Excel - Date Parsing issue

Build
,
#1

Hello,

Issue: I am having trouble with a date formatting issue using parsing.

GOAL: Convert all dates in column G from yyyy/dd/MM to ddMMyyyy

Process:

  1. I am using an excel application scope to read my file and create an output data table.
  2. I then use a ‘for each row’ activity to loop through the rows. I am using an assign activity to create a variable “myDateTime” = Row(“Effective Date”).
  3. I then use another assign activity to parse the date (DateTime.ParseExact(row(“Effective Date”).ToString,“yyyy/MM/dd”,System.Globalization.CultureInfo.InvariantCulture).ToString(“ddMMyyyy”).
  4. I then use a write range activity to write the data from the DTData table to the file specifying the column to return the values. I receive an error message "The string is not a recognized as a valid date/time.

image
image1532×802 113 KB

#2

Hi @MF.RPA

Did u checked the preserve format option in read range ?

1 Like
#3

@MF.RPA
the best is to debug the code and to inspect the datatable content, as it can differ from the visual presentation of the data in excel.

Issues for the not valid … exception could be:

  • empty values
  • the need of trimming the values
  • different datetime format within the datatable column give a try on MM/dd/yyyy HH:mm:ss

Also use the demo from below and do run an analysis job on the data. Just adopt the format as by your needs:
FindNotMatchingDateFormatDates.xaml (6.8 KB)

1 Like
#4

@MF.RPA

Check the output of the Datatable and see in which type Date column is appearing.

IF you are seeing it as a number then check below
https://forum.uipath.com/t/tutorial-convert-number-to-date-format/249171/3

Else

Share the screenshot of the Output Datatable

Hope this helps you

Thanks

1 Like
#5

I enabled preserve formatting and the application ran for 15 minutes and nothing happened.

I would also like to mention that the format in the excel file is appearing as yyyy/MM/DD but when I click format cells and select a different date format the values do not change / format into the newly selected format. Right now I have the dates formatted as “General”

#6

The Excel recognized format is different than using DateTime.ParseExact(). But, it appears as though one of the values you are trying to convert is not in the yyyy/MM/dd format.

I would suggest always using an IF condition before converting to make sure you are converting a valid value…

You can use DateTime.TryParseExact() to check the value.

Then, what you can do is find out which value is not in the correct format. And, if there are values with end spaces, just add .Trim to the string. If you find that there are multiple date formats, you can use the format as an array (I think), like {“yyyy/MM/dd”,“yy/MM/dd”}.

1 Like
#7

One more thing, in your screenshot, you have the wrong format used, as compared to your number #3 point which is correct:
DateTime.ParseExact(row(“Effective Date”).ToString,“yyyy/MM/dd”,System.Globalization.CultureInfo.InvariantCulture).ToString(“ddMMyyyy”)

So just need to add .Trim and a TryParse

IF, DateTime.TryParseExact(row(“Effective Date”).ToString.Trim,“yyyy/MM/dd”,System.Globalization.CultureInfo.InvariantCulture)
   Assign DateTime.ParseExact(row(“Effective Date”).ToString.Trim,“yyyy/MM/dd”,System.Globalization.CultureInfo.InvariantCulture).ToString(“ddMMyyyy”)
1 Like
#8

Hello,
Sorry for the delayed response, I had taken my computer in for service and it took a few days to get it back. I am still unable to figure this issue out. I tried to add an if activity and my assign activities in the THEN section but I received a complier error. Am I overlooking something basic here?

Thank you,

bug
bug1545×724 34.7 KB

#9

It looks like your quotation marks are the wrong character. Try replacing your quotes character with the quotation character again. Sometimes if you copy stuff from the forums, it does this.

1 Like
#10

Here is the solution for anyone still following this issue:
DateTime.ParseExact(EffectiveDate,“yyyy/MM/dd”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MMddyyyy”)

1 Like