DateTime Manipulation - String was not recognized as a valid DateTime

Help Studio
, ,
1

Hi,

I have an excel with date column, values as below. first one is in mm/dd/yyyy format and second one is in dd/mm/yyyy format
6/11/2020
16/06/2020

On fetching the column value using – Row.Item(“Date Of last contact”).ToString
it shows -
06/11/2020 00:00:00
16/06/2020

When i do any kind of date time manipulation, It is giving below exception for 16/06/2020 format -
Assign: String was not recognized as a valid DateTime.

I did - DateDiff (DateInterval.Day, Convert.ToDateTime(DateContacted),Convert.ToDateTime(TodayDate) ).ToString

where DateContacted = value getting from excel

Can anybody help me where I am going wrong?
Thanks

2

Hi,

Can you try with this
int_days = DateDiff(DateInterval.Day, DateTime.ParseExact(Strinput.ToString, “dd-MMM-yyyy”, System.Globalization.CultureInfo.InvariantCulture),DateTime.now())

Change format as per your input string,

Refer this for datetime

3

Hi @Shuchi_Gupta ,

Try the below :
DateContacted = DateTime.ParseExact(Row.Item(“Date Of last contact”).ToString,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)

Then use the DateContacted variable (of type date time) to anykind of manipulations needed.

Regards,
Rohith

4

Hi @Shuchi_Gupta

How about this expression?

DateDiff (DateInterval.Day,DateTime.ParseExact(Row.Item(“Date Of last contact”).ToString,"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy"),DateTime.now.Tostring(dd/MM/yyyy)).ToString

Regards
Gokul

5

@rohith.prabhu - i believe this wont work as date is in different format in excel. while fetching Row.Item … it will come in different format and we need to give the exact format in DateTime.ParseExact(Row.Item(“Date Of last contact”).ToString,“dd/MM/yyyy”,

Correct me if I am wrong.

6

Hi @Shuchi_Gupta ,

As Inpsected by checking the date values, we observe two types of date formats.

dd/MM/yyyy
MM/dd/yyyy HH:mm:ss

We would need to parse the date value accordingly with these formats that have been found, to get the right date time value.

Hence, First we Parse the DateContacted variable value that is retrieved from Excel like below.

dateFormats = {"dd/MM/yyyy","MM/dd/yyyy HH:mm:ss"}

DateContacted_Date = DateTime.ParseExact(DateContacted,dateFormats,System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None)

Here, the DateContacted_Date variable is of the type DateTime
Now, the above should return the DateTime value which we can directly use in DateDiff.

DateDiff (DateInterval.Day, DateContacted_Date,Convert.ToDateTime(TodayDate) ).ToString

This should give you the right output.

Let us know after you have checked the above statements.

7

Hi @Shuchi_Gupta - we can pass multiple formats in the same expression as below to cover all possible formats appearing in the input

DateContacted = DateTime.ParseExact(Row.Item(“Date Of last contact”).ToString,{“dd/MM/yyyy”, “MM/dd/yyyy HH:mm:ss”},System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None)

Regards,
Rohith

8

Hi @Shuchi_Gupta

How about this expression ?

ArrString → Array(String) format

ArrString = New String(){"MM/dd/yyyy","dd/MM/yyyy hh:mm:ss","MM/dd/yyyy hh:mm:ss","dd/MM/yyyy"}

DateDiff (DateInterval.Day,DateTime.ParseExact(Row.Item("Date Of last contact").ToString,ArrString,System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("dd/MM/yyyy"),DateTime.now.Tostring("dd/MM/yyyy")).ToString

Regards
Gokul

9

in addition to @supermanPunch also find following firstAid sheet introducing to this topic and also showing analysis techniques

:ambulance: :sos: [FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues - News / Tutorials - UiPath Community Forum

Same as discussed by Arpan the multiple formats were handled with a format array

About the DateDiff function take a note that not fractions of the selected unit is returned. When this is needed / important let us know for presenting additional techniques

10

@supermanPunch Thanks. It worked.

1 Like
11

@ppr sure will check.

12

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