cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Steffijeff
Frequent Visitor

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 II
Super User II

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (68,752)