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

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

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!

Top Solution Authors
Top Kudoed Authors
Users online (6,580)