Writing dates from PowerApps to Excel

[AndrewCrompton]

I am getting some strange behaviour when writing dates from my app to Excel (I'm using the "Excel Online (Business)" connector).

Here is what the data look like as displayed in PowerApps tables:

The upper table shows data from a collection in the app. StartDate and EndDate are both date-values (ThisItem.StartDate and 

ThisItem.EndDate respectively). This is all as expected.

 

The lower table shows data from the Excel table: here StartDate is a number and EndDate is text. If I view the data using Excel itself, I see exactly the same thing. In Excel, I can format StartDate as a number, or I can format it as a date: both display in the expected way. I cannot get EndDate to display as anything other than what is shown above, as is to be expected given that it is text.

 

The Reload icon shown above to the right of "Data on attendance sheets (5)" does the following:

RemoveIf(tAttendanceSheet_CourseData_1, true) ;

Patch(tAttendanceSheet_CourseData_1, collCourseDataForAttendanceSheets) ;

collCourseDataForAttendanceSheets is the collection in the app containing the source-data (as shown in the upper table above);

tAttendanceSheet_CourseData_1 is the name of the Excel table to which the app connects (as shown in the lower table above).

So this code first clears out all the existing data in the Excel table, then writes out new data.

Everything seems to work just fine except that StartDate gets written out as a number and EndDate gets written out as text. I cannot see any difference between these two within the app (as noted, both are dates within the app) so I cannot understand why they are getting written out differently.

If anyone has any helpful suggestions, I'd be very glad to hear them.

Thanks. Andrew

[OdinDelToro]

Hello @AndrewCrompton ,

What's the data type in your excel file tAttendanceSheet_CourseData_1 for the columns mentioned StartDate and EndDate?

You can verify by selecting the first value of the column and looking for the type.

Usually, the data type on the column is what determines how it's displayed so if you have number for the StartDate, select the column and then change the type to date.

Hope this helps clarify the doubt, let me know in case you're still facing an issue within it.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[AndrewCrompton]

Hi, Odin

Thanks for your response.

I had already checked out the data-types in the Excel file: StartDate gets written out as a number and EndDate gets written out as text.

It is precisely this discrepancy that I cannot understand, given that I cannot find any relevant difference between StartDate and EndDate in my app.

Andrew.

[OdinDelToro]

Hello @AndrewCrompton ,

Please, can you send more details about the collection code for collCourseDataForAttendanceSheets?

[AndrewCrompton]

Hi, again

Here is the code for the collCourseDataForAttendanceSheets collection:

ClearCollect(

collCourseDataForAttendanceSheets,

ShowColumns(

Filter(DropColumns(tCourseUnits, "Title"), CourseUnitCurrent = true),

"CourseUnitID", "TypeOfCourse", "NumberOfWeeksOrEvents",
"Programme", "CourseTitle", "Period", "CourseFee",
"StartDate", "EndDate"

)

);

As you can see, this collection takes its data from tCourseUnits, which is a SP list. The settings for StartDate and EndDate in that list are identical. When I view tCourseUnits in SP, this is how those two columns display for the rows illustrated in my original post:

If you can see anything here that gives a clue as to why these two fields behave differently when written out to the Excel file, you have sharper vision than I do!

Thanks, Andrew

[OdinDelToro]

Hello @AndrewCrompton ,

Code for the collection and patch is correct. The issue is when the patch updates the file tAttendanceSheet_CourseData_1. As the data type is assigned already, when it updates it will remain as how it's in the excel file. So, if you update the data, even if it's coming as expected from SharePoint, it will keep the format to be displayed as on the target file. 

Please, change the column data type on the excel file to be as expected StartDate from number to Date and EndDate from text to Date.

To do that, open the excel file, and click on the column to select it all and change the type.

After that, all the column will have a Date type which will display as follows:

Then try to patch again to test.

Hope this helps clarify the doubt, let me know in case you're still facing an issue within it.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[AndrewCrompton]

Thank you for the suggestion, which I followed. Result: no change in the way the dates are handled in the Excel file.

I went further: 

In the app, I removed the connection to Excel.

In Excel, I removed the StartDate and EndDate columns, also the __PowerAppsId__ column. Then I inserted new StartDate and EndDate columns, formatted as Date.

Finally, back in the app I recreated the connection to Excel (which added back the __PowerAppsId__ column).

Result: exactly as before! At least it's consistent 😁.