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

[Emejcz]

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.

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.

[iltoningui]

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]

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. 

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:

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:

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.

Thanks!

Gary

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