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

Excel Online Create Table - Date automatically converts to some decimal number

Hello

Once I create table for some data with Excel connector there is some unwanted date conversion which ruins whole thing.

 

Once table is created date automatically displays as a number instead of some standard date format.

 

Emejcz_0-1638455028676.png

Any idea there is a function to format this integer to some date?

I know it is some kind of value calculated from 1jan1900 but no idea what to do with that inside the flow.

2 REPLIES 2
iltoningui
Resolver IV
Resolver IV

Hi @Emejcz 

 

Try using Office scripts for excel on the Web.

For automating excel files tasks.

 

Hope this helps.

Consider marking this as a solution 

GaryS
Microsoft
Microsoft

Within the Excel connector itself there are advanced options available. Try expanding the advanced options and selecting ISO 8601 for the DateTime Format and see if that resolves your issue. 

GaryS_0-1639069803163.png

There is also an issue trying to read a date from an Excel sheet and copy it elsewhere. For this problem, you'll need to add an additional conversion. I'll give you a simple example. Let's say I have an Excel spreadsheet in my SP document library called Students Test.xlsx. The spreadsheet/table looks like this:

GaryS_1-1639071615250.png

When the date gets read by Power Automate, it's parsed as text. Excel stores dates in Serial Date format which means that a date like ‘2019-01-10' will be read as ‘43475' within Power Automate. In order for the date from Excel to format correctly on a SP list I would need to apply the following function conversion:

 

if(empty(item()?['BirthDay']),null,addDays('1899-12-30',int(item()?['BirthDay']),'yyyy-MM-dd'))
 
The end result will look like:
GaryS_2-1639072242622.png

If I add another student to the Spreadsheet, delete the conversion formula, then try re-running my flow I actually get an error because the date I chose (8/14/2002) for his birthday is getting copied into Power Automate as 37482. If I fix my SharePoint Create Item action to use that formula for Birthday rather than the generated dynamic content, it succeeds and adds the new line(s) to my SP list.

GaryS_5-1639073270567.png

 

GaryS_3-1639072760904.png

Thanks!

Gary

 

If this Post helps, then please consider Accept as solution to help the other members find it more quickly.

Helpful resources

Announcements
Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (1,841)