Converting dates is challenging and having a function that does that for us is pretty amazing. The DateTimeValue function does that for us. It converts a string into a date-time object.
So let’s see how we can convert the dates easily.
It follows a simple pattern.
- String to convert
DateTimeValue("2021-09-30") will return 30/09/2021 00:00
Let’s try with time:
DateTimeValue("2021-09-30 12:34") will return 30/09/2021 12:34
Looks good. Let’s try spelling it:
DateTimeValue("September 30 2021 12:34") will return 30/09/2021 12:34
We can even ignore the year
DateTimeValue("September 30") will return 30/09/2021 00:00
I want to point out a small detail here. You’ll see the possible formats in the limitations, and the one above is represented with a comma. Power Apps is smart enough to ignore separators.
Although the DateTimeValue function is super versatile, Microsoft decided to only convert dates in the following formats:
- MM/DD/YYYY or MM-DD-YYYY
- DD/MM/YYYY or DD-MM-YYYY
- YYYY/MM/DD or YYYY-MM-DD
- MM/DD/YY or MM-DD-YY
- DD/MM/YY or DD-MM-YY
- DD Mon YYYY
- Month DD, YYYY
If you’re not familiar with the formats:
- MM – Month in 0-12 format
- DD – Day in the 0-31 format
- YYYY – Year
- Mon – Month abbreviation, like “JAN” for example
- Month – Month spelled like “January.”
There are a lot of formats, and notice that it’s possible also to use multiple separators, but no other variation is possible.
It’s a good move from Microsoft. It’s a good balance between the most common representations of dates and limiting the subset of types to make it more manageable for the team to
Dates can change the format
Note that just because you “see” the date in that format doesn’t mean that the date is saved as such. Power App displays the dates according to your preferences, so don’t consider this representation “static” when building your UI. Also, believe in your UI the max possible characters of a data, including translations. It’s super ugly to have truncated or not well-formatted dates.
Don’t convert the dates to strings.
You don’t need to convert the dates, so only do it to define a format for everyone in your company. I would advise against it because changing things based on the regional settings is a lot nicer (and it’s free) than having a date that people don’t understand.
This is NOT a way for Natural Language Processing.
Some apps enable you to insert the date as you want in a text box, and they will try to convert the date automatically. It’s called “Natural Language Processing,” It enables you to insert dates like above, but also “next Tuesday at 5 pm”. Please note that there are limits on the conversions of dates, so don’t use this as a pseudo- “natural language processing” engine.
DateValue, TimeValue, and DateTimeValue functions in Power Apps
Back to the Power Apps Function Reference
Photo by Jonathan J. Castellon on Unsplash