cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dates from Excel to SharePoint - has something changed?

Hi all,

 

I have a flow which transfers data from a spreadsheet to a SharePoint List. Four of the columns are date columns and I have been using the addDays('1899-12-30', int(ColumnGoesHere) trick to get it to work.

 

This morning the flow failed because the int() function could not be applied to the date fields. On closer inspection of the List Rows action, the dates fields are actually pulling through as dates, rather than the date serial number. I was able to work around the problem by changing the date column types to General, so that the date serial number pulled through and the flow ran correctly, but I'm not sure if I should make a more permanent change to my flow.

 

For example, with the column in Excel formatted as Date, Flow fetches:

"Candidate Offer Response Date":"2020-11-23T00:00:00.000Z"

With the column in Excel formatted as General, Flow fetches:

"Candidate Offer Response Date":"44158"

 

Is anyone aware of a change in how Flow handles dates from Excel?

 

Thanks

Joel

2 REPLIES 2
AndyJ42
New Member

I used a slightly different expression and mine is still working:
 
addseconds('1899-12-30',int(formatnumber(mul(float(outputs('Get_a_row_(Latest_Row)')?['body/DateColumn']),86400),'0')))
 
EnoraFr
New Member

Hi,

I tryed this solution but I have an error : The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

 

so I tried to put another int in my expression : 

addseconds('1899-12-30',int(formatnumber(mul(float(int(ColumnName),86400),'0')))

but I have the same error with the int : 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

I also Tried :

formatDateTime(addseconds('1899-12-30',int(formatnumber(mul(float(items('Apply_to_each')?['Requested Finish']),86400),'0'))),'g')

but I had the same issue

I was wondering if the problem come from the seconds so I tried to split my value : 

addseconds('1899-12-30',int(formatnumber(mul(float(int(first(split(string(variables(ColumnName)),'.'))),86400),'0'))) (but It doesn't work)

 

Have you got any tip ?
thank you

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

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.

Users online (1,681)