cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Clayton1
Frequent Visitor

DateTime and Excel Connector Problems

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

4 REPLIES 4
Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Clayton1
Frequent Visitor

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
Community Support
Community Support

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

Clayton1
Frequent Visitor

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.

Clayton1_0-1615618513466.png

 

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

Clayton1_1-1615618600086.png

Photo 3 - The data being dumped in the Excel Sheet

Clayton1_2-1615618768062.png

Regards, 

CG. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,243)