Problem converting Excel date data with function AddDays
I'm trying to get data from excel to sharepoint. My table have columns that contains date information. I'm having a problem of using AddDays, when I use my date data in an expression, the value returned is null.
In the Excel Table the column "Data Início" is formatted as Date.
The flow has the following sequence:
List Rows present in a Table
Initialize variable (Type: String)
Apply to each: Value
First, I did a test using the expression addDays('1899-12-30',item()?['DataInício'],'yyyy-MM-dd') as bellow:
but the flow fail and the message was:
It was not possible to process as model language expressions in the action's' Set_variable 'entries in line' 1 'and column' 9826 ':' A language function in the model 'addDays' expects its second parameter to be an integer. The value provided is of type 'Null'. Check usage details at https://aka.ms/logicexpressions#adddays
So, I modified the expression, using int(): addDays('1899-12-30',int(item()?['DataInício']),'yyyy-MM-dd'), but it could not return an integer, cause the values from item()?['DataInício'] are null:
Instead of item()?['DataInício'] , I also tested with item()('Aplicar_a_cada')?['DataInício'] and item()('Listar_linhas_presentes_em_uma_tabela')?['DataInício']and it wasn't succeed.
But, my table values are not null. Then, I used the dynamic content from column "Data Início" to see what happens and the values appears:
While writing the expression, the specific data "Data Início", doesn't appears as an option to use. Only these:
They talk about how excels dates are numbers counted from January 1, 1900. I'm not saying that this will solve your issue in getting the data, but it might help with parsing it, and adding everything.
Additionally, it wouldn't hurt to maybe add a Condition in case any vital data *is* empty, but that's down the line.
The rest of my message is just regarding the question at the beginning regarding using a SharePoint list instead of Excel.
Plus, not for nothing, SharePoint lists will pass date information much more easily to Flow.
But, mainly, I only asked for two reasons:
To maybe open a different way of thinking about the data that you're putting in the spreadsheet.
To use the most suitable program for the data (I don't know the data, so this is an assumption!)
That second point I make (kindly 🙂 ...) because Excel is basically a calculator, and a SharePoint list is essentially a basic database. If you're storing date data, or names, etc, it seems to me that it might potentially be better kept in a SharePoint list.
With the SP list, then, you have more (easy) options of where the data can travel, next.
If someone at your firm (or yourself) is adamant that they have an excel sheet, there's simple built in exporting functions.
Similarly, if someone needs reports on that data, a regular report can be set up, or you can even make a cool one with Power Automate! 🙂
Equally, on the reports front, why create more documents? Why not have a static SharePoint page designed for the person/people that need the data, with a list webpart and a view that's tailored for them. It can be printed, too!
It'll come with built in input forms (and validation), and you can set up the lovely Microsoft Forms to input via Power Automate.