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
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,642)