Dates from Excel to SharePoint - has something changed?

[Anonymous]

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

[AndyJ42]

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')))

There is a good explanation here: https://ryanmaclean365.com/2020/07/28/converting-excel-date-time-serial-values-with-power-automate/

[EnoraFr]

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