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
Dual Super User

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

6 REPLIES 6
Pstork1
Dual Super User
Dual Super User

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.
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. 

Gatchaman
Frequent Visitor

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.

Gatchaman_0-1642437676970.png

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.

Gatchaman_1-1642437861101.png

 

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

 

Sorry for the bother and thank you for your time.

 

 

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.



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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,601)