Date Validation

[MCrawley2]

I'm on a roll today, this must be the third issue I've come across and I'm trying to do basic stuff.

Right now I'm trying to validate a date string to validate as a valid date so I'm using DateValue("yyyy/mm/dd) (or other varieties) but what I'm finding is that if you give it a date that isn't a valid date but IS still numeric, for example "2019/12/99" it will return a completely different date as if it has parsed the string correctly but obviously the Date value is wrong. I would have expected this to return null or blank but it doesn't.

I've checked for forum and I can see other people have had similar issues, but this exact problem of invalid numerical hasn't come up as far as I can see. The only suggestion I've seen is to pass in the country code (eg. "fr") but this does not fix the problem, it just makes sure it parses the month in the right place.

For now I do have a nasty workaround which is basically calling DateValue then checking if the value in the returned month match the original date passed in but this obviously isn't good:

Returns False because there aren't 32 days in Jan:
Notify("Valid?: " & IsMatch(Text(Month(DateValue("2019/12/32","fr"))),"12"))
Returns True because there are 31 days in Jan:
Notify("Valid?: " & IsMatch(Text(Month(DateValue("2019/12/31","fr"))),"12"))

I'm aware I could do a regex validator as well but this wouldn't be nice given leap years etc, plus the built in function should validate and return a useful response.

Any ideas how I can get this raised?

[StalinPonnusamy]

Hi @MCrawley2 

One easiest option is to restrict the user to type. So that they can use the Calendar Selection. To do Set IsEditable property to false.

[StalinPonnusamy]

Hi @MCrawley2 

We can validate the date entered is valid date or not like

!IsBlankOrError(DateValue(Text(DatePicker7.SelectedDate)))

Sample:

False

True

[MCrawley2]

Unfortunately none of the above solutions work for us - Due to the nature of the business I work in (UK Government) we work with very strict design guidelines have cannot use Date Pickers to collect dates, instead we have to have 3 separate date inputs for Day, Month, Year. This isn't something I'm able to control.

Also just to note that if you were to manually set the .Value of a DatePicker with an invalid date, such as 30/02/2000 then the date picker would show a different date rather than erroring out.

We actually have a Microsoft consultant direct from Microsoft working with us at the moment that recommended we raise the issue as a support ticket as it does appear to be a genuine bug, so I believe the support team are looking into this at the moment.

I will update if I get any news for anyone else in a similar situation.

[StalinPonnusamy]

Hi @MCrawley2 

The date function converts the wrong date to the nearest actual/correct date instead of throwing an error. For example, If I select 2019/2/29 (yyyy/mm/dd), and the Date function converts to 2019/3/1 which is correct.

Workaround:

We can display the message to the user if the selected date is converted 

Converted Date :

Text(Date(YearDropdown.Selected.Value, MonthDropdown.Selected.Value,DayDropdown.Selected.Value), "yyyy/mm/dd")

Is Changed Flag:

Text(Date(YearDropdown.Selected.Value, MonthDropdown.Selected.Value,DayDropdown.Selected.Value), "yyyy/mm/dd") <> (YearDropdown.Selected.Value & "/" & If(MonthDropdown.Selected.Value < 10, "0" & MonthDropdown.Selected.Value,MonthDropdown.Selected.Value)  & "/" & DayDropdown.Selected.Value)

Thanks,
Stalin - Learn To Illuminate

[MCrawley2]

Appreciate the response and we also had a similar hack but again this isn't enough.

Validating that the returned date matches the date originally input doesn't work because there are still edge-cases where the same date is returned, despite the date being invalid - For example, "30/02/2000" and "31/02/2000".

An easy way to test this is to add a label and set its Text property to an invalid DateValue result.

For example:

DateValue("2000/02/28") returns the same date
DateValue("2000/02/29") returns the same date (and should because its a leap year)
DateValue("2000/02/30") returns the same date which it shouldn't because its not a valid date so your solution would not work
DateValue("2000/02/31") returns the same date which it shouldn't because its not a valid date so your solution would not work
DateValue("2000/02/32") returns the wrong date which would be okay with your above suggestion

It seems having a day value greater than 31 is the crux of this issue - The datevalue function appears to have very primitive valudation logic if that's the case.

[MCrawley2 · Accepted solution]

Further to this, I've now had response from MS Suppport Team who have advised basically that the way DateValue works is intended functionality. They did suggest database level validation or a PowerAutomate flow, so that might be an option for some folk.

In our situation, it seems we have been able to come up with a working solution which accounts for leap years as well as invalid dates. First, concatonate your date into a variable (DateString) then your validatio logic will look like:

IsBlank(Text(DateValue(DateString))) Or !(Text(DateValue(DateString), "yyyy/mm/dd") = DateString)

[rgraauw]

This works perfectly!! Thanks