Difference between two dates in days - where is error in my workflow?

Hi,
I have CSV file from where I should get two dates and find diffrence in days between them.
I tried all types of commands, variable types etc. Nothing is working…
Please help.

Process looks like that:
Date_compare.zip (12.3 KB)
Date 1 e.g.: A1 = 16.04.2019
Date 2 e.g.: B1 = 4/17/2019 9:45:35 AM

  1. Read CSV (I uploaded it) => DT1
  2. For each row => get row item (A1 and A2)
  3. Assign: A1 = A1.ToString.Replace(".","/")
  4. Assign: A2 = DateTime.ParseExact(A1.ToString(),“dd/MM/yyyy”,Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)
  5. Assign: B1 = B1.ToString.Replace(".","/")
  6. Assign: B2 = B1.ToString.Substring(0,10)
  7. Assign: B2 = DateTime.ParseExact(B2.ToString(),“dd/MM/yyyy”,Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)
  8. Assign: diff = A2.DayOfYear - B2.DayOfYear

Variables Types:
A1, B1 => GenericValue
A2, B2 => DateTime
diff => double
DT1 => DataTable

Why it doesn’t work?

Hi,
In B1 you need not to do using substring try with yourdate.tostring.toshortdate this will gve you only date and not time.

@Sevlir

Buddy, I think B1 is of type GENERIC and is getting assinged to B2 of type DATETIME…here comes the first error.

So kindly change it like

B2 = DateTime.ParseExact(B1.ToString.Substring(0,10),“dd/MM/yyyy”,Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)

Hope this would work

Cheers…


Doesn’t work :frowning:


Also tried Your solution, but id didn’t works :frowning:

Hey,
Sorry try with date.toshortdatestring

Regards,
Pavan H

1 Like

B1.Tostring.ToShortDateString will be of string type but you are assinging it to a B2 datetime datatype…Kindly apply here datetime.parse
B2 = DateTime.ParseExact(B1.Tostring.ToShortDateString,“dd/MM/yyyy”,Globalization.CultureInfo.InvariantCulture)

Cheers

2 Likes

Hi,

This might help you
DateDiff(DateInterval, Date1,Date2)
DateDiff(DateInterval.Day, “2016-01-30”,“2016-01-30”).

here date 1 and date 2 are of type string

let us know if this helps,
Regards,
Pavan H

1 Like

“DateTime.ParseExact(B1.Tostring.ToShortDateString,“dd/MM/yyyy”,Globalization.CultureInfo.InvariantCulture)”.
Element “ToShortDateString” is not part of elemnt “String”.
So, still nothing ;(

This is my workflow:
Date_compare.xaml (12.4 KB)

Can you try to change it to be correct, so I can understand where I’m making error? I spent last 3 days trying to understand what I’m doing wrong, but I’m hitting the wall… no results…

1 Like

Thank You, but this stage in workflow works fine. I have problem with date convertion… ;(

1 Like

Hey,
Use this in your assign statement
DateTime.ParseExact(A1.ToString,“dd/MM/yyyy”,Globalization.CultureInfo.InvariantCulture)
to assign to datTime type.
same to B2 also.

let us know if this helps
Regards,
Pavan H

1 Like

Hi,
in assign try to A2= DateTime.parseExact(A1.ToString,“dd/MM/yyyy”,Globalization.CultureInfo.InvariantCulture)
this will assign the string value to date type.

similar to B2 and then you can get the date diff as mentioned in above post.

Let us know if this helps,
Regards,
Pavan H

3 Likes

It works, and it gives me all good answers, but at the end error occures:

Source: Assign
Message: String was not recognized as a valid DataTime
Exepction Type: System.FormatException

What is wrong if all stages works fine?

Hi,
Can you screenshot the assign activity with values you are passing.
try a trim along with the .tostring.trim and see if that works.

Regards,
Pavan H

1 Like

I did it, but still error occures.

Here are the PrtScr:

2 3 4 5 6 7 8

1 Like

Hi, instead of using convert.todatetime use date.parseexact which is already there in previous posts.

Regards,
Pavan H

1 Like

Ok but when I am using it

the error ocurres:

So if I don’t use
Assign: B1 = Convert.ToDateTime(B1).ToString(“dd/MM/yyyy”)
There will be an error:

1 Like

I can use the issue_date field as string and manipulate string to correct date and using DateTime.Parse:

  1. For each row => get row item
  2. Get 2 value from row
    2.1. Assign: issue_string = row(“issue_date”).ToString
    2.2. Assign: acceptance_string = row(“acceptance_date”).ToString
  3. Check IF issue_string and acceptance_string aren’t blank.
    3.1. Assign: issue_array_string = issue_string.Split(".“c)
    3.2. Keep mind correct datetime parse with format “MM/dd/yyyy”
    3.3. Assign: issue_string = issue_array_string(1)+”/"+issue_array_string(0)+"/"+issue_array_string(2)
    3.4. Assing: issue_date = DateTime.Parse(issue_string)
    3.5. Assign: acceptance_date = DateTime.Parse(acceptance_string)
    3.6. Assign: diff = DateDiff(“d”, issue_date, acceptance_date)
    3.7. write line or message box: “Issue Date: “+issue_date.ToShortDateString+” - Acceptance Date: “+ acceptance_date.ToShortDateString+” - Diff Value: " + diff.ToString + " day(s)”

See my result:
result

2 Likes

Now workable, ish workflow looks like that:
Main.xaml (12.4 KB)

Date 1 e.g.: A1 = 16.04.2019
Date 2 e.g.: B1 = 4/17/2019 9:45:35 AM

  1. Read CSV (I uploaded it) => DT1
  2. For each row => get row item (A1 and A2)
  3. Assign: A1 = A1.ToString.Replace(".","/")
  4. Assign: A2 = DateTime.ParseExact(A1.ToString.Trim,“dd/MM/yyyy”,Globalization.CultureInfo.InvariantCulture)
  5. Assign: B1 = B1.ToString.Replace(".","/")
  6. Assign: B2 = Convert.ToDateTime(B1).ToString(“dd/MM/yyyy”)
  7. Assign: B2 = DateTime.ParseExact(B1.ToString.Trim,“dd/MM/yyyy”,Globalization.CultureInfo.InvariantCulture)
  8. Assign: diff = B2.DayOfYear - A2.DayOfYear

Variables Types:
A1, B1 => GenericValue
A2, B2 => DateTime
diff => double
DT1 => DataTable

but at the end of process allways ocurres error:

2 Likes

If you open your excel as text editor, u will see empty values in last row.
empty_values

You can check value is not empty with if activities.

2 Likes