Dates from Excel to Sharepoint

[Johany_Navarro]

Hope you all are ok.

Getting frustrating with this issue, two weeks ago this formula was working when reading dates from Excel and updating them into Sharepoint items.

if(empty(item()?['YearEnd']), null, addDays('1899-12-30', int(item()?['YearEnd']), 'yyyy-MM-dd'))

today when I was trying to do the same I'm getting this error.

Unable to process template language expressions in action 'Update_item' inputs at line '1' and column '20240': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

to see if Power Automate was getting the data correctly I created another flow that reads the same excel and creates an HTML table and sends it to my email.

The strange thing is when I check the table I noticed this:

Title     YearEnd

CompanyA  '22/04/2021'

CompanyB  44308

CompanyC  '22/04/2021'

CompanyD  44308

Any idea why some dates are read as numbers and others as, I think, as text??  

[NiloferA]

Hello @Johany_Navarro ,

In Excel Files, when you type in a Date and press enter, you will see that it gives it a specific Format and if you select the cell and notice the Type of Data it stores at the ribbon, it will say "Date"

Now, this Date can be represented as Number too, if you simply change the Type as Text or Number, you will see that its converted to some numbers.

**This is how dates are stored by default

**I copied the date and changed the type to Text and this is the result

You can go to your Excel File and check out the type of Data in the respective cells where you are encountering this issue of Dates being fetched as Numbers and see if you change the Type to 'Short Date' and run your Flow again if it solves your problem.

Let me know the outcome of this analysis.

Please give a Kudo to this reply if you found this helpful!

[Johany_Navarro]

Hi @NiloferA, thanks for taking the time to help me.

I already did it, it doesn't matter what type of data I set up... Text, Number or Date all of them show me the same result.

I also thought of the cells have number format so I don't need to use the int() in the formula see below.

if(empty(item()?['YearEnd']), null, addDays('1899-12-30', item()?['YearEnd'], 'yyyy-MM-dd'))

however same issue same error

[NiloferA · Accepted solution]

Hello @Johany_Navarro ,

That's strange because when I checked in a sample flow I created, the int() gives you an error if you are trying to convert "22/04/2021" but works perfectly fine when I pass 44308 from the excel.

Anyways, I have a work around for your situation here, let's forget about what the type of date might be in excel (date, text or number), we will handle this situation in your flow itself.

We will configure the Flow in such a way that if it encounters date as Number, it will execute the formula you have and update it in SharePoint else if it fails that means it has encountered date as Text then we will format it and update the date accordingly.

1. In the beginning of your Flow, Initialize an array Variable (MyArray), we will need this if the date is captured as string
2. Now, in your Apply to Each while iterating over the Excel Rows, add the below actions and configure them as mentioned
   1. Keep your update item action as is which is trying to convert the date from Excel to int and then executes the addDays formula
   2. Below it, add Set Variable action and set the input as - split(item()?['YearEnd'],'/') and set its "Configure run after" as when the above Update Item has failed
      1. 
      2. 
   3. Next, Add Update Item action again, and this time try to update the date as - formatDateTime(concat( variables('MyArray')[2], '-', variables('MyArray')[1], '-', variables('MyArray')[0]), 'M/d/yyyy')
      1. 
3. After the Looping action, add a Terminate action and set the Value to Succeeded, just so that the overall status of your Flow is set to Succeeded.

Please Give a Kudo if you found this helpful and mark this Reply as a Solution if this solved your problem!

[Johany_Navarro]

Hi, @NiloferA I'm so grateful to you for spending time on this.

Unfortunately, I didn't have time to carry on testing solutions cause the flow had to run back on Friday 😞 I had to go on every singles date, press F2 and enter then finally run the process.

You absolutely deserve kudos and a beer.

Many many many thanks. 

[NiloferA]

No problem, @Johany_Navarro. 

I feel for you though, it must have been tiring! Thank you for the Kudo! 😉

[Frederick_Nerk]

I know this must have been raised before but I thought I'd raise it again.  SharePoint is a Microsoft product and Excel is a Microsoft product.  Why then are we force to write convoluted scripts to import dates from Excel into Sharepoint?  

I want to bring in date/times from a spreadsheet to create calendar event start / finish times.  I have to import the date, calculate the int to add days, use trunc and a function to convert to time in the date, add all this together just to get a date with a time other than midnight.

Why is it this hard?  Why have they not yet created a dateExcel2SP function to do all this in the background?  Why is it this hard?