Answered

Datepicker in excel database, HELLLLLPPP

(0) Share

Report

Posted on by Magoforrest

HI all,

I am having some trouble with my dates in an Excel database, let me explain my self better.... i am selecting a date from a datepicker in an certain app that sends it's results to an Excel sheet, after that i have an app that reads that sheet and it should display that date... I am having trouble because what i get from the second app is a number....all fields on Excel are set as general because if I set them as "short date" i will get no result... please, if you know how to fix the issue let me know because i am staring at the monitor now for hours 🙂

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Magoforrest 59 on at

Like (0)

Report

datepickerforum2.png

datepickerforum.png

Was this reply helpful? Yes No
Magoforrest 59 on at

Like (0)

Report

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report
Dates in excel are stored as a whole number which represents the days (and fractional days) since 12/30/1899. So to translate that number back into a date you need to use the following formula.

Text( DateAdd( DateTimeValue("1899-12-30 12:00:00 AM"), RoundDown( (Value(ValuefromExcel) * 86400), 0 ), TimeUnit.Seconds ), ShortDateTime )

Explaining from the inside out

Value from Excel multiplied by the number of Seconds in a day (86,400)

Rounded Down to no decimal places to get the number of seconds since 12-30-1899

Add that to 12-30-1899 to get the date

Use Text() to format it as a Date Time String.

Was this reply helpful? Yes No
Verified answer

v-qiaqi@microsoft.com on at

Like (0)

Report
Hi@Magoforrest,

Do you want to read the Date in a Text format?

Could you please tell me that how you save the Date Picker selected date to the Excel sheet?

I think the key is how you save the date to the Excel.

I have a test on my side, please take a try as below.

Add a Button and set the OnSelect property as below:

Patch(Table1,Defaults(Table1),{Title:TextInput1.Text,Date:Text(DatePicker1.SelectedDate,"[$-en-US]dd/mm/yyyy")})

Notice that you should format your selected date into a Text string when you save date to Excel sheet.

Then in another app, please set the Text of the Label as below:

LookUp(Table1,Title="1",Date)// Title is a primary key in my table, please replace it with yours.

Please check if the above could help, if not, please show me how you save the selected date.

Regards,

Qi

Was this reply helpful? Yes No