Data Import from Excel - Dates / Times (Date Only, Time Only)

Like (0) Share

Report

Posted on 7 Jun 2022 08:54:50 by pp365

402

Dear Power Apps Community,

The data for my Canvas App is sourced from an Excel file (.xlsx). This is a one-time (per App session) import process; once the data is in App, it is worked on within collections, etc. The Excel data needs to be directly from a standalone Excel file and not 'connected' via/to Excel Online due to the number of rows.

The issue I'm having is that to select the segment of the data the user needs to work with, they need to pick from a dropdown with a date and a second dropdown with a time. Those date and time values are fetched from the Excel file data source ('Distinct' from a Date column and a Time column).

The Excel file datasource is an export from a vast database and I want to try and avoid the end user having to manually apply formatting to the source data to avoid errors. But what I'm not sure on is how to restrict the 'Date' dropdown in Power Apps to only show the date, and the 'Time' to only show the time. Currently what I'm getting is:

Date Dropdown: dd/mm/yyyy tt:tt

Time Dropdown: dd/mm/yyyy tt:tt

This should be:

Date Dropdown: dd/mm/yyyy

Time Dropdown: tt:tt

In Excel I have inspected the formatting of the source columns. For date, this is "Category: Custom" and in type, "dd-mmm-yy".

For time, this is hh:mm.

So, in Excel, the formatting of the cells appears accurate in the formatting desired - as if Power Apps is forcibly adding time to date and date to time respectively.

I've added some screenshots of the dropdowns in the App and the formatting from Excel for the date column and time column.

Thank you very much for your help Power Apps community,

Edd

Categories:

Using Apps Using Formulas

General Questions

Excel-FormatDate.JPG

Excel-FormatTime.JPG

Date-Time-Pickers...

All responses (5)

Answers (0)

pp365 402 on 09 Jun 2022 at 18:24:45

Like (0)

Report
Re: Data Import from Excel - Dates / Times (Date Only, Time Only)

Hi @timl ,

Thanks, again, for your help with this. I am replying quickly now in case any of the Community are wanting to use the solution provided.

In brief, I have got it working, the code you provided was a big help but I found there were still several steps I had to take to shape the data so it appeared as I needed it and then worked with the rest of the App. Naturally, these are part App-specific and part out of scope of my original question, but for the Community benefit I'm aiming to document these and provide the steps I took - but I'll have to get back to this.

One important note for now, there was an extra bracket in the code which caused the 'Add Columns' to fail, so no bracket to close that part off, so should be:

ClearCollect(colNewCollection, AddColumns(colExistingCollection, "CeremonyDateTime", DateTimeValue(Text(CeremonyDate, "yyyy-mm-dd") & " " & Hour(CeremonyTime) & ":" & Minute(CeremonyTime)) )
pp365 402 on 09 Jun 2022 at 08:07:57

Like (0)

Report

Re: Data Import from Excel - Dates / Times (Date Only, Time Only)

Thanks very much @timl for getting back to me, it's much appreciated. I'll try that fix and confirm accordingly.
Thank you again!
timl 34,955 Super User 2025 Season 1 on 08 Jun 2022 at 11:31:59

Like (0)

Report
Re: Data Import from Excel - Dates / Times (Date Only, Time Only)

@pp365

The way I would approach this is to combine the 2 "ceremony date" and "ceremony time" columns into a single "ceremony datetime" column in your underlying collection.

To build a collection, you would use this type of formula (assuming that the name of your 2 source columns are CeremonyDate and CeremonyTime):

ClearCollect(colNewCollection, AddColumns(colExistingCollection), "CeremonyDateTime", DateTimeValue(Text(CeremonyDate, "yyyy-mm-dd") & " " & Hour(CeremonyTime) & ":" & Minute(CeremonyTime)) )

If you then work from this new collection, it should hopefully solve the problems that you're experiencing.
pp365 402 on 08 Jun 2022 at 08:08:18

Like (0)

Report

Re: Data Import from Excel - Dates / Times (Date Only, Time Only)

Hi all,

I had an idea to use Left and Right to essentially trim off the unneeded data in the display for the user in the drop down. Whilst that worked fine for the visibility of the data in the drop down, I could not then use the drop down as a filter on the original data at source, as the two didn't match.

I collected the data from the source in to a collection then used a 'Left' or 'Right' on that collection to display the data in the drop down - in other words, the data is still matching the data at source in the collection. What I am now stuck on is how to relate back the item selected in the drop down to the "source" data in the Collection which could then be used to match against the data at source.

Any help from anyone on this would be greatly appreciated, I've wasted several hours on this and presumably this issue is not exclusive to me. Thanks very much all!
pp365 402 on 07 Jun 2022 at 17:24:19

Like (0)

Report

Re: Data Import from Excel - Dates / Times (Date Only, Time Only)

Hi @WarrenBelz , @RandyHayes , @timl ,

Please excuse the direct approach here but as regular 'Solution Providers', I was wondering if you could take a look at this issue if you have a moment. I'm hoping to bring this App to production very shortly and am unsure how to resolve this.

I would be really grateful for any responses from any of the community members and thanks again!