cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Date formatting issues - excel power apps spreadsheet

Hi

 

I have imported some dates from Excel into my powerapp.

 

When I add one of the dates to a label using "ThisItem.Forecast_x0020_Launch", I get a date value of 43091 displayed. 

 

If I enter 43091 into excel and format it as a date, it is 22/12/17 - so far so good.

 

However, when i use the following function to convert the datevalue into text in powerapps, it displays 01/01/09:

Text(DateValue(ThisItem.Forecast_x0020_Launch), "[$-en-US]dd/mm/yy")

 

The formula is obviously wrong...can someone help please?

 

Thanks

 

Kai

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Power Apps
Power Apps

Re: Date formatting issues - excel power apps spreadsheet

@AndyPennell's solution would be the cleanest one - get the data from Excel in a proper format.

 

But if you cannot change the source, you can use the following formula to convert the number to a date:

Text(
    DateAdd(Date(1900, 1, 1), ThisItem.Forecast_x0020_Launch - 2, Days),
    "[$-en-US]dd/mm/yy")

Numbers are converted into dates in Excel using the value 1899-12-30 as the base (which is why we're subtracting 2 from 1900-01-01, which is the earliest value that can be created using the Date function).

View solution in original post

7 REPLIES 7
Highlighted
Power Apps
Power Apps

Re: Date formatting issues - excel power apps spreadsheet

I would recommend changing the column type in Excel then re-importing. It is coming in as a number to PowerApps, which cannot be easily converted into a Date type.

Highlighted
Anonymous
Not applicable

Re: Date formatting issues - excel power apps spreadsheet

 Thanks for your response Andy - The columns in excel are already in date format - is that what you mean?

  

 

 Excel Date columns.JPG

Highlighted
Power Apps
Power Apps

Re: Date formatting issues - excel power apps spreadsheet

@AndyPennell's solution would be the cleanest one - get the data from Excel in a proper format.

 

But if you cannot change the source, you can use the following formula to convert the number to a date:

Text(
    DateAdd(Date(1900, 1, 1), ThisItem.Forecast_x0020_Launch - 2, Days),
    "[$-en-US]dd/mm/yy")

Numbers are converted into dates in Excel using the value 1899-12-30 as the base (which is why we're subtracting 2 from 1900-01-01, which is the earliest value that can be created using the Date function).

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Date formatting issues - excel power apps spreadsheet

Thank you Carlos - that has worked.

 

If Andy can't come back with a better, cleaner solution that I can get to work, then I will use yours 🙂

 

This is my first try at creating an App, so I'm sure I will have more questions to come!

Highlighted
Power Apps
Power Apps

Re: Date formatting issues - excel power apps spreadsheet

In Excel: right click on the column, Format Cells, choose Date. Then re-import into PowerApps. Hopefully they will come in as actual dates.

Highlighted
New Member

Re: Date formatting issues - excel power apps spreadsheet

Where is reimport? I see only refresh

Highlighted
New Member

Re: Date formatting issues - excel power apps spreadsheet

 I delete table and insert one more time and its ok

thanks

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,214)