DateValue Issue

[Migrated_User]

Content migrated from TechNet. Post attribution to:PK Hong

I am using DateValue(Text(Today()), "fr"), as well as "da", "zh-TW".

All are showing 1/2/2017, except "en" and "zh-CH" showing 2/13/2016 (this is correct today).

Please check is there is something wrong with Language_code.

TQ

---

PK Hong

[Migrated_User]

The issue is that you're trying to parse a date in the incorrect format. I'm assuming your computer is in a configuration where the date format is set to mm/dd/yyyy, so the result of Text(Today()) is going to be "2/13/2016" (assuming that "Today" is February 13th, 2016).

Now, if you try to convert that string to a date using the French format (which is dd/mm/yyyy), you will have the following components:

• day: 02
• month: 13
• year: 2016

That date is invalid (there are only 12 months in the year), so the function DateValue is doing its best to convert that to a date, assuming that the 13th month of 2016 is actually January 2017, and you'll end up with January 2nd, 2017 as your date value. And that's what you end up with in most of the language codes, since most countries use the dd/mm/yyyy format.

---

Carlos Figueira

[Migrated_User]

The issue is that you're trying to parse a date in the incorrect format. I'm assuming your computer is in a configuration where the date format is set to mm/dd/yyyy, so the result of Text(Today()) is going to be "2/13/2016" (assuming that "Today" is February 13th, 2016).

Now, if you try to convert that string to a date using the French format (which is dd/mm/yyyy), you will have the following components:

• day: 02
• month: 13
• year: 2016

That date is invalid (there are only 12 months in the year), so the function DateValue is doing its best to convert that to a date, assuming that the 13th month of 2016 is actually January 2017, and you'll end up with January 2nd, 2017 as your date value. And that's what you end up with in most of the language codes, since most countries use the dd/mm/yyyy format.

---

Carlos Figueira

[Migrated_User · Accepted solution]

I'm afraid the advice on the other post you mentioned is misleading. We should not be using DateValue to format a date to be displayed for a specific locale. DateValue is used to convert from a text value to a Date/Time value - and what you want to do is exactly the opposite (convert from Date/Time to a text to be displayed). 

If you want to display a date in a specific format, you should use the Text function, as you mentioned that it worked for you:

Text(Today(), "dd/mm/yyyy")

If you want to take a user's input (from a text input, for example), and convert it to a Date/Time value (e.g., to manipulate that value, to send it to aa function that expects a date, etc.), then you should use the DateValue function

"30 days from today: " &
    Text(DateAdd(Today(), 30, Days), "dd/mm/yyyy")

---

Carlos Figueira

[Anonymous]

Hi @Migrated_User

I'm able to add a DefaultDate using the formula:  Text(Today(), "mm/dd/yyyy" )

and the date displays correctly, albeit in U.S. format  (e.g. 3/27/2017)

As soon as I change the formula to:  Text(Today(), "dd/mm/yyyy" )

the date becomes incorrect (e.g. 3/3/2019).

When I click back on the date field, the DefaultDate formula has changed to include a [$-en-US] value:

Is there anyway to stop this from happening?  Thank you!

[AndyPennell]

DefaultDate requires an actual date, and you are giving it a string, which it is trying to re-interpret (subject to your locale). Just set it to

Today()

if you would like it to default to the current day.

The format of the date shown in the picker is determined by the locale the app is running in.

[AndyPennell]

FYI the date picker displays the date using the equivalent of

Text(date,DateTimeFormat.ShortDate)

which will display in a format dependent on the current locale.