Answered

Saving Dates in year-month-day format

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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.

SourceData.xlsx

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (2)

Verified answer

Pstork1 69,024 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
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?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

Pstork1 69,024 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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!!

Was this reply helpful? Yes No
eka24 20,923 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Pstork1
Thanks for the help.

Was this reply helpful? Yes No