Date in a text label to be used as proper date in DATEDIFF

[FelipeCaru]

Hello all,

I am trying to make a DATEDIFF between a chosen date in a DateValue control as date 1, and a date that is read from an Excel sheet (and it is formatted as date) that is, it seems, text, as date 2. I can't do the DATEDIFF due to this. What can I do?

"Fin plan" is from the datacard with same name that has inside a label "datacardvalue" and "datacardkey", and if I use the label from it, and a ". " for properties, the first one to show up is ".Text" so it is not valid, it seems, for a datediff. What can I do?

[RandyHayes]

@FelipeCaru 

You will want to convert the text to a DateValue.  DateDiff needs date values to compare.

So, consider this formula:

DateDiff(DateValue(finPlan.Text), DateValue(finReal.Text), Days)

So in the above, substitute the two text items with the field, column, control, or whatever else you have that is giving you a Text Date.

I hope this is helpful for you.

[FelipeCaru]

@RandyHayes I used a label to test if the DateValue worked, but it didn't give back the same date. Would it be because the one in text is on spanish format (dd/mm/yyyy)? The DateValue one gave me a date on 2020 or so

[RandyHayes · Accepted solution]

@FelipeCaru 

Yes, that's exactly right.  For "21/02/2019" - when doing a DateValue on that in en-US, you will get Sept 2 2020

What you will need to do is specify the locale for that date...

DateValue(finPlan.Text, "yourlanguageTag")

[FelipeCaru]

That was exactly what I needed/was missing @RandyHayes , thank you very much