DateTime and Excel Connector Problems

[Clayton1]

Hi All,

First time poster, and very new to the PowerApps platform. Would really appreciate some assistance on this!

I'm building an application that is push/pulling data from an Excel Online Spreadsheet.

I can not get the application to write data to the spreadsheet in the format that I require; I simply require the date pushed from the application, to the sheet in "dd/mm/yyyy" format, without the timestamp. 

Unfortunately, I cannot share screenshots due to the nature of the data, but here is some info on my current PowerApps/Excel configuration:

1. I have a form that is connected to the data source; A table on the Excel Online Spreadsheet.
2. The OnClick function is working fine; All other data is being pushed to the sheet successfully when the form is submitted.
3. The Date/Time being pushed to the Excel Online Spreadsheet is formatted as: 2021-02-28T13:00:00.000Z.
4. Within the date picker, DateTimeZone value is set to: DateTimeZone.Local, and Format is set to: "dd/mm/yyyy"
5. The respective column within the Excel Online Spreadsheet is set to "dd/mm/yyyy".

Any guidance you can offer would be greatly appreciated.

Thanks!

CG.

[Pstork1 · Accepted solution]

Date only columns in Excel are considered to be the date at midnight.  They always have both Date and time.  The format you are pushing to Excel is an ISO 8601 format and the Z at the end means its in UTC timezone.  The format you really want is yyyy-mm-dd 00:00:000. That is midnight for whatever date.  Although you use that format to push the date to Excel, Excel will display it however you have the spreadsheet configured.  So take the datefrom the datepicker, format it as Text using "yyyy-mm-dd" then concatenate that string with " 00:00:000" to get a date only string to send to Excel.

[Clayton1]

Hi Pstork,

Thanks so much for your quick response, and I really appreciate you taking the time to explain this for me! 

Thanks a million, I will try this a little later tonight.

Regards,

CG

[askondo]

@Pstork1 Thank you for all your contributions to the forum. I appreciate it very much.

[Clayton1]

Hi Pstork,

I've just tried concatenating the values, and I'm still unable to successfully push the data over to Excel as desired. 

I'll attach some photos of the environment that aren't information sensitive - Please let me know if you need any more info to assist. 

Photo 1 - The default data card that I'm working with, as pulled from Excel.

Photo 2 - The default Data format, as seen when the date picker is selected. 

Photo 3 - The data being dumped in the Excel Sheet

Regards, 

CG. 

[Gatchaman]

Hello I'm sorry to add a message to the thread since it is marked as solved. 

But I am having a similar issue and I am not sure I understand @Pstork1 solution.

This is what I understood for the datepicker setting.

and for the card I kept the selectedDate (but I tried pointing it to arrivalDatePicker.Format without success.

@Clayton1  did you manage to find the solution in the end?

Sorry for the bother and thank you for your time.

[Pstork1]

You get a better response if you post this as a new thread.  Most people don't look at old threads unless they are looking for an answer, like you are.  If the answer here doesn't help, I suggest posting it as a new thread and we'll help you figure it out.  That way the next person will have an easier time finding it.