Convert string (text) date to a date value

[Steffijeff]

Hi PowerApps community

I've been searching the archives for a solution and have not yet found one to my problem, so here goes. 

My app is linked  to a SP list. Within my app I have a Date Picker control. The SP column this links to is single line of text. I need to keep it as single line of text as I need to be able to return "N/A" to the column when certain conditions are met. When creating a new item, this works fine and does as I need. The problem arises when I try to edit the list item. 

I have tried a variety of Text and DateValue formulas but whenever I do this, the date entered into the date picker is totally random. 

E.g. Date at creation is 17/04/2021. Date on SP list is 17/04/2021. Date in app when I edit 04/05/2022!

I can't figure out why. Please help

Thank you, kindly

[iAm_ManCat]

Hi, is the datepicker trying to use Parent.default as its default?

Could you share the defaultDate property of the datepicker please?

[CarlosFigueira · Accepted solution]

This is likely a problem with how the date is being parsed. If the date 17/04/2021 is read as Month/Day/Year, then it will be interpreted as the day 4 of the month 17 of 2021, which spilling over to the next year becomes the day 4 of month 5 of 2022 (the value that you get). Depending on the locale of the user where the app is running, different rules are used for parsing dates from text values, so if you always have the dd/mm/yyyy format, you can use something like DateValue(<text value>, "fr-FR") and it should interpret the parts correctly.

[Steffijeff]

@CarlosFigueira Absolutely perfect! Has worked a treat, thank you so much 🙂

[Steffijeff]

Hi @CarlosFigueira, 

Your solution solved the immediate problem but now when I resubmit the form it passes the date 17/04/2021 back to my SharePoint list as 1618614000000?!?!

I have put your suggested formula in the Default property of the Data Card and the Update property is set to DatePicker.SelectedDate. Should this be something else?

[CarlosFigueira]

What is the type of the column in SharePoint? If it is text, you can force the date to the format that you want by having the Update property set to something like Text(DatePicker.SelectedDate, "dd/mm/yyyy")

[Steffijeff]

@CarlosFigueira 

The SP list is single line of text (and needs to be kept this way so I can get "N/A" in there). The app is used for creating a new SP item and for any subsequent edits of that item. 

When creating an item in the app, I have a date picker linked to the SP column.

When I click on the submit button, the date appears in the SharePoint column as expected

When I edit the item via the app the date appears as it should as per the first visual (thanks to your solution above!). However, when I then resubmit the form, the date in my SP list changes to this. 

This happens regardless of whether I actually change the date or not.

I have the following properties on the data card

Default: DateValue(ThisItem.'Date of system fix',"en-GB")

Update: SystemFixDateEdit.SelectedDate

And the following properties on the datepicker itself

DefaultDate: Parent.Default

Any thoughts?

[CarlosFigueira]

If you know exactly which format you want for the date, you can change the Update property on the data card to use the Text function to convert in the desired format:

Text(SystemFixDateEdit.SelectedDate, "dd/mm/yyyy")

[Steffijeff]

When I type that in, it automatically changes it to

Text(SystemFixDateEdit.SelectedDate,"[$-en-US]dd/mm/yyyy")

And the output on SP list is now