cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Steffijeff
Helper I
Helper I

Convert string (text) date to a date value

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

1 ACCEPTED SOLUTION

Accepted Solutions
CarlosFigueira
Power Apps
Power Apps

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.

View solution in original post

8 REPLIES 8
iAm_ManCat
Super User
Super User

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

Could you share the defaultDate property of the datepicker please?

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


CarlosFigueira
Power Apps
Power Apps

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.

View solution in original post

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

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?

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")

@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.

Steffijeff_0-1619187903116.png

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

Steffijeff_1-1619190140610.png

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. 

Steffijeff_2-1619190383054.png

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?

 

 

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")

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 

Steffijeff_0-1619191269088.png

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,374)