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

Help
#1

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?

#2

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

#3

@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…

#4

Zrzut%20ekranu%20(85)
Zrzut%20ekranu%20(85)1190×689 31.5 KB

Doesn’t work :frowning:

#5

Zrzut%20ekranu%20(87)
Zrzut%20ekranu%20(87)926×756 28.2 KB

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

#6

Hey,
Sorry try with date.toshortdatestring

Regards,
Pavan H

1 Like
#7

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
#8

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
#9

“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
#10

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

1 Like
#11

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
#12

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
#13

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?

#14

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
#15

I did it, but still error occures.

Here are the PrtScr:

1
11229×737 51.5 KB
2 3 4 5 6 7 8

1 Like
#16

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

Regards,
Pavan H

1 Like
#17

Ok but when I am using it

1
1884×200 8.89 KB

the error ocurres:

2
2869×311 13.7 KB

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

3
3819×320 18.1 KB

1 Like
#18

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
#19

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:

1
1736×550 38 KB

2 Likes
#20

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