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

Saving Dates in year-month-day format

Hi everyone,

Following instructions from a popular power apps book for saving dates in year-month-day format. According to the PowerApps book the following should work provided my source excel column is formatted as text. this is what is in the 

 

DateTimeValue(Left(Parent.Default, 4) & "-" & Mid(Parent.Default, 5,2) & "-" & Mid(Parent.Default, 7,2) & " "& Mid(Parent.Default,9,2) & ":" & Mid(Parent.Default,11,2))

 

I have followed all the directions and still no joy, it's complaining that it is expecting text in the left & mid functions. Any help is appreciated! Attached is a screen shot and excel file.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

The fact that the Data Card has a Date control in it, unless you unlocked it and added it manually, would suggest that the field being brought in from Excel is NOT formatted as a text field. I replicated your setup and when I added the data card for the date column it comes in as a text field.  The only way to get a datepicker is to unlock the card and add it manually.  AFter doing that I got the date loaded when I used the following.

DateTimeValue(Concatenate(Left(Parent.Default,4),"-",Mid(Parent.Default,5,2),"-",Mid(Parent.Default,5,2)))

That's essentially the same as yours except I used the Concatenate function instead of the '&' operations.  I don't think the issue is with the formula.  I think the problem is the data mapping in your Excel file.  Did you format the cells as Text in the table or leave it as General? 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Pstork1
Dual Super User III
Dual Super User III

I would remove the whole card itself and re-add it to the form.  Since it was originally a date card, its expecting the input to be a date.  That's why Parent.Default isn't registering as a string.

 

When you re-add it the card will come in with a textbox.  Just unlock the card, remove the text box (or hide it) and add a datapicker.  Then setup your formula and it should work.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

6 REPLIES 6
Pstork1
Dual Super User III
Dual Super User III

The fact that the Data Card has a Date control in it, unless you unlocked it and added it manually, would suggest that the field being brought in from Excel is NOT formatted as a text field. I replicated your setup and when I added the data card for the date column it comes in as a text field.  The only way to get a datepicker is to unlock the card and add it manually.  AFter doing that I got the date loaded when I used the following.

DateTimeValue(Concatenate(Left(Parent.Default,4),"-",Mid(Parent.Default,5,2),"-",Mid(Parent.Default,5,2)))

That's essentially the same as yours except I used the Concatenate function instead of the '&' operations.  I don't think the issue is with the formula.  I think the problem is the data mapping in your Excel file.  Did you format the cells as Text in the table or leave it as General? 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Hi thanks for your input. I selected the date column and formatted it as text. The date picker was from when I started my app using the auto generate from excel data feature. I then unlocked the card for the date and removed the hour and minutes picked and all references to them.

Pstork1
Dual Super User III
Dual Super User III

I would remove the whole card itself and re-add it to the form.  Since it was originally a date card, its expecting the input to be a date.  That's why Parent.Default isn't registering as a string.

 

When you re-add it the card will come in with a textbox.  Just unlock the card, remove the text box (or hide it) and add a datapicker.  Then setup your formula and it should work.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

That's IT!!! you pointed me in that direction and I did exactly as you just suggested put new card in and new date picker and it works! Thank you!!

Please mark @Pstork1 reply as the Solution if it has been solved

@Pstork1

Thanks for the help.

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

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (58,088)