Blog

Dates - How to convert dates from MM DD YYYY to DD MM YYYY and vice versa

This post describes how to convert dates in the US format "mm dd yyyy", to "dd mm yyyy" format.

A common task is to convert text representations of dates in US "mm dd yyyy" format, to the "dd mm yyyy" format that's used in most places in the world, such as the United Kingdom and Australia.

We carry out this task by calling the DateValue function to convert the text representation to a date object. We then call the Text function to format the result in the desired "dd mm yyyy" format.

Converting dates from "mm dd yyyy" to "dd mm yyyy"

To demonstrate this task, here's an example of how to convert 2nd October 2022 from "mm/dd/yyyy" to "dd/mm/ yyyy" format.

Text(
     DateValue("10/02/2022", "en-US"),
     "dd/mm/yyyy"
)
The screenshot below illustrates the output.


Converting dates from "dd mm yyyy" to "mm dd yyyy"

To convert an input value from "dd mm yyyy" to "mm dd yyyy" format, here's an example of how to convert the date 2nd October 2022.

Text(
     DateValue("02/10/2022", "en-GB"),
     "mm/dd/yyyy"
)

Here, we specify that the input value is in "dd mm yyyy" format by passing the language code for Great Britain ("en-GB") to the DateValue function. Note that the language code doesn't specifically need to be "en-GB" - it just needs to be a locale that uses the "dd mm yyyy" date format.

The screenshot below confirms that the formula produces the correct output.


What to do when converting dates doesn't work

Occasionally, app builders attempt to convert dates to different formats using formula that doesn't work. On these occasions, the app produces a random date that's possibly years in the past or future. Alternatively, it may produce a date with missing date parts.

There are generally 2 reasons for this type of failure. The first is that the formula omits the language code in the call to DateValue. An example of this is shown below.
Text(
     DateValue("02/10/2022"),
     "mm/dd/yyyy",
)

If we don't specify a language code in the call to DateValue, Power Apps applies the language of the user, which is based on the language setting of the device or browser. If the browser language doesn't match the date format of the expected input value, it explains a common situation where the date conversion works for a most users, but fails for a minority of users. Therefore, it is always best to specify an explicit locale (eg, "en-GB", "en-US") when calling the DateValue function.

The second common reason for date conversion failures is confusion over the language code that we pass to the Text function. The language code we pass to the Text function specifies the language of the output format. Where formula specifies both input and output language codes through the DateValue and Text functions, it's easy for app builders to get these mixed up, which results in a formula that doesn't work as expected.


To highlight an example of why we would pass a language code to the Text function, here's an example of how we would output the long month name in Spanish.

Text(
     DateValue("02/10/2022", "en-GB"),
     "mm/dd/yyyy",
     "es-ES"
)
The screenshot beneath shows the output of this formula.

Conclusion

This post described how to convert dates from "mm dd yyyy" to "dd mm yyyy" format (and vice versa), and highlighted two common reasons why date conversion formula fails to work as expected.
Previous
Next
Related posts