October 13, 2024

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.

There are other two functions, the DateValue and the TimeValue that parse dates and times individually. But you can also provide to DateTimeValue function a date without a time and it will work.

So let’s see how we can convert the dates easily.

Usage

It follows a simple pattern.

  1. String to convert
Converting dates is super tricky because dates can be represented in different ways depending on where you are. For example, 5/6/2021 can be the 5th of June or the 6th of May

Example:

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.

Limitations

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

Recommendations:

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.

Sources:

DateValue, TimeValue, and DateTimeValue functions in Power Apps 

Back to the Power Apps Function Reference

Photo by Jonathan J. Castellon on Unsplash

Manuel Gomes

I have 18 years of experience in automation, project management, and development. In addition to that, I have been writing for this website for over 3 years now, providing readers with valuable insights and information. I hope my expertise allows me to create compelling, informative content that resonates with the audience.

View all posts by Manuel Gomes →

One thought on “Power App: DateTimeValue function

  1. This came so close to being what I was looking for—I want to use data from a simple table (Date, Received, Disclosed) such that for [Date] a daily email is sent that sends the row of [Date] with an email saying “[Received] received, [Disclosed disclosed]”.

    It’s crazy Microsoft didn’t build some Excel date functionality into the backend to be able to deal with this… can you help me?

Leave a Reply

Your email address will not be published. Required fields are marked *

Mastodon