Date Time format problem

Hi I am having issues with one of the values in the data table(read from an excel file), for some odd reasons it’s in MM/dd/yyyy format even though in excel it’s in dd/MM/yyyy format.

I am trying to use compare date activity to calculate the difference in days( but for that to happen i need to convert data table date value to dd/MM/yyyy format. and i am converting that using “Convert.ToDateTime(GRI_SentDate.ToString(“dd-MM-yyyy”))" and now i am getting an error “Assign: String was not recognized as a valid DateTime”. though i did notice it did change the format for first 2 values.

Is there any other way to calculate the difference in days?

Let’s do one thing
Pass the datatable to FOR EACH ROW activity and inside that use a WRITELINE activity where mention the value as row(“yourcolumnname”).ToString so that we can know the actual date format
Kindly share that here
Based on that we can frame a expression
Cheers @Vishnu.Vijayan

@Palaniyappan thanks for the quick reply, this is output that i am getting.

01/05/2020 00:00:00

to convert this as a datetime variable
use a assign activity like this
datetime_output = Datetime.ParseExact(row(“yourcolumnname”).ToString.SubString(0,10),“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture)

where datetime_output is a variable of type System.DateTime

Cheers @Vishnu.Vijayan

It still doesn’t work, the output date is in “MM//dd/yyyy” format, though i did try by making a small change to the syntax form **datetime_output = Datetime.ParseExact(row(“yourcolumnname”).ToString.SubString(0,10),“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture) to **datetime_output = Datetime.ParseExact(row(“yourcolumnname”).ToString.SubString(0,10),“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture) and it threw an error “Assign: The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.

Though there is 1 thing i noticed if i convert this date to string to “dd/MM/YYYY” form it did change the format, but then again i can’t use a string for date comparison.

@Vishnu.Vijayan The Date Values that you need to compare are both values from a datatable ?

@supermanPunch there are two situations. In the first Scenario it will compare the date with System’s current Date and in the second comparison the date will be taken from the user and will calculate the difference in days.

@Vishnu.Vijayan Convert the Date from Datatable using :
Datetime.ParseExact(row(“DateColumn”).ToString,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture) if you are getting date format as “MM/dd/yyyy”

Then You can Directly Compare with DateTime.Now

It’s not working because whenever i am using the compare date activity, i am getting the output as 1

@Vishnu.Vijayan Do you have to use Date Comparison Activity?

Yes i need to because i need to know the difference in days and accordingly i need to take actions. i even tried CDate(My variable name(which is a datetime datatype)) and CDate( and it always gives output as 1. now in that excel sheet the Month is Jan, so probably its comparing the month.

Yes i need to because i need to know the difference in days and accordingly i need to take actions. I believe Datediff is the best but i am not sure what code to use. both the System.datetime and Datatable’s date are in mm/dd/yyyy format

@Vishnu.Vijayan If you have two Date Objects then you dont need to use DateDiff or Date Comparison Activity, You can use like this : (Date1-Date2).Days

@supermanPunch Thank you so much for the response, now i am using assign activity and this is the code, “(GRI_SentDate-System.DateTime.Now).Days”, GRI_Sentdate is a datetime variable, now its giving an error “Cannot assign from type ‘System.Int32’ to type ‘System.DateTime’ in Assign activity”.

@Vishnu.Vijayan That expression returns an Integer value, You needed the difference in days right ? :sweat_smile:

Kind of you can say. I will tell you in detail what the scenarios are:

Scenario 1: From the excel date calculate the difference difference in days(System.Datetime.Now), then if the Difference in days is more than 3 days, then that row needs to be copied in a new datatable.

Scenario 2: A user will give a date input, then it will compare the date from the datatable, if the difference is greater than 0 then add the row to a new data table.

So to use the IF condition i think it must be an integer :slight_smile:

I am really sorry for asking these Childish questions, I am HR professional, not that good with coding :smiley: :smiley:

@Vishnu.Vijayan What is GRI_SentDate ? The Date from Excel ? Also did the Expression work? Also How will the user Give the Date, in which format?

@supermanPunch yep, GRI_SentDate is the date variable from the excel.

Nope the expression didn’t work it threw an error “Cannot assign from type ‘System.Int32’ to type ‘System.DateTime’ in Assign activity”.

The user date i still need to think about it. Ideally he will choose it from Calendar which we will ask him to choose(if possible), else will ask to write the date in text box(hence will be a string) in MM/dd/yyyy format as from the above scenarios i believe uipath assigns date in this format

@Vishnu.Vijayan What is the variable you used to assign that expression ? And Also What is the Date Format of GRI_SentDate ?

@Vishnu.Vijayan Can you give me Excel File, for the Scenario 1, I can try and build a Workflow for it