cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sandyqueir
Regular Visitor

Problem converting Excel date data with function AddDays

Hello, 

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.Excel.png

 

 

The flow has the following sequence:

  • List Rows present in a Table
  • Initialize variable (Type: String)
  • Apply to each: Value
    • Define Variable

First, I did a test using the expression addDays('1899-12-30',item()?['DataInício'],'yyyy-MM-dd') as bellow:

Power Automate5.png

 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:
 
Power Automate3.png

 

Power Automate4.png

 

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: 
 
Power Automate.png

 

PowerAutomate1.png

 

While writing the expression, the specific data "Data Início", doesn't appears as an option to use. Only these:Power Automate6.png

 

  
Could anyone help me to solve this?
1 REPLY 1
eliotcole
Power Participant
Power Participant

Hi, @sandyqueir , purely asking this out of interest, not casting any aspersions: Is there a reason to use Excel for the data source and not a SharePoint list?

 

Anyway, I would suggest a read of this Solved issue here:

https://powerusers.microsoft.com/t5/General-Power-Automate/Manipulating-Flow-Excel-Date/td-p/365876

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:

  1. To maybe open a different way of thinking about the data that you're putting in the spreadsheet.
  2. 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.

 

Sorry, I went on for a bit there.

 

Looking purely at your data, though,

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,590)