cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

Converting the date format when writing to excel

Hello,

 

I am trying to write the date on which a Sharepoint item was created to an excel table.

I want it to look like the top one in the picture below, but it comes up like the bottom one and in excel I cannot reformat it to a date. 

schwibach_0-1610395951930.png

 

Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
Resolver I
Resolver I

Ok, 
so I ended up finding a solution.
Here it is. In Excel you reference the cell that you got into excel from SP and use the following formula in a cell:
=DATEVALUE(LEFT(yourSharePointDateGoesHere;10))+TIMEVALUE(MID(yourSharePointDateGoesHere;12;8))

View solution in original post

5 REPLIES 5
Community Support
Community Support

Hi @schwibach,

 

You could use the formatDateTime() function convert the date time to the formate you like when you add a row into a table or update a row, for example:

formatDateTime(utcNow(),'dd.MM.yyyy hh:mm')

Screenshot 2021-01-12 101342.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Resolver I
Resolver I

Hello,

 

thanks a lot! It displays things correctly in excel.
However, the dates don't work in the same way as the ones I get from Forms when it gives me the date and time submitted.

As far as the data goes, it seems to me that what I get in excel from Forms is a number that Excel converts to a date.

And Powerautomate now sends an already converted date, that excel display correctly, but does not calculate correctly... if that makes any sense.

 

The last entry in the following table comes from the Flow I built with your formula (thanks again for that!)
The other entries come from a flow that adds rows whenever a form is submitted to Forms.

I have conditional formatting in that table that will highlight entries that are above average (as in: newest) in green and the last 10 % red, so the person monitoring can tell at a glance whom they should maybe ask to update their numbers. 

 

schwibach_0-1610491797134.png

This is really a minor thing, but now that I'm getting into dates, I'm curious if it can be resolved.

 

For some background: I am giving 8 different groups of people two options to submit data to that table. The heads of the organisation will be able to use a powerapp to submit data and check when and what their organisation submitted last. And they can delegate submitting data to other people by giving them the link to their organisation-specific Form. 

I thought about it a bit more.

When I change the format (in the Excel cell) of the date that we added from SharePoint with Powerautomate, it still comes up as a date, whereas the other cells will come up as something like 44209,22

 

Is it possible to make powerautomate send the date and time as the type of number that Excel will understand. I could then have Excel display it as a date and use it to calculate.

Then it should work correctly.

Community Support
Community Support

Hi @schwibach,

 

PowerAutomate couldn't update calculate column in Excel table, after append value to calculate column, the formula wouldn't work.

And sorry it is impossible to send the date and time as date type of number to Excel. Only supported is text date.

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Resolver I
Resolver I

Ok, 
so I ended up finding a solution.
Here it is. In Excel you reference the cell that you got into excel from SP and use the following formula in a cell:
=DATEVALUE(LEFT(yourSharePointDateGoesHere;10))+TIMEVALUE(MID(yourSharePointDateGoesHere;12;8))

View solution in original post

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (90,768)