cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Error when trying to import date from excel online to SP item.

 

Hi,

I'm new to Flow, but I'm trying to have an item in a SP list, created when a new line is entered in a Excel Online table.

 

I have a flow that looks for change of file in folder as a trigger.

The it pulls a list of rows in the Excel table.

Then I try to "Use on each" to create the items.

 

 

I think the issue is that I have a lot of date fields which returns the value as ex. 41293. I tried to solve it with the expression  "addDays('1899-12-31',int(item()['Slutfakturadato']),'yyyy-MM-dd')"

But it comes up with an error:

 

8 REPLIES 8
DominikPetri
Frequent Visitor

Does your date in Excel also includes the time? Then the number is a float and can't be converted into an integer using the int() function.

 

Also note that you have to add days from 12-30-1899 (and not 12-31) due to the leap year bug in Excel (which is not present in Flow)

 

Hope this helps

Dominik.

Anonymous
Not applicable

Hi Dominik,

 

No they only have the date. And I can see the data coming from the Excel in the flow test and it is a number ex. 41245

 

Anonymous
Not applicable

And I just changed the date as You wrote and nothing changed. It's still the same error. 😞

Hi @Anonymous,

 

Could you please share the screenshots of the error message and output of excel?

You can insert photos by clicking the "camera" button:

Annotation 2019-11-14 160005.png

 

I just tested your expression and it is no problem:

Annotation 2019-11-14 160109.png

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

2019-11-14 09_03_27-Window.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This is the current expression in all fields just changing th reference: addDays('1899-12-30',int(item()['Slutfakturadato']),'yyyy-MM-dd')

 

This is the error that comes up:

2019-11-14 09_10_38-Window.png

 

 

 

Hi @Anonymous,

 

It should be some error with the type of "Slutfakturadato", could you please share the output of List Rows present in a table action?

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

This is from "List rows present in a table" (Excel):

{
  "@odata.context": "https://excelonline-ne.azconn-ne.p.azurewebsites.net/$metadata#drives('b%21krJ2u4xX7U-hCUoCFvoaoa3RkHaUW1pBr5YSWT6LL4iu4vXAfPTRRpFMS0Wqamvy')/Files('013R6GQ7GEQCWBBXKTIVALMWWKPIO3KQ7K')/Tables('%7BE4B0D3C7-30DF-45DE-9367-459CE7D4C1CE%7D')/items",
  "value": [
    {
      "@odata.etag": "",
      "ItemInternalId": "78390f4b-04f0-4895-9eb4-c4d07aa697e6",
      "Eltel AO": "1121496",
      "AO status": "Slutfaktureret",
      "Slutfakturadato": "43062",
      "Forespørgsel modtaget - Dato": "",
      "Kl_x002e_ xxxx": "",
      "Pris afgivet - Dato": "",
      "Kl_x002e__x002e_ xxxx": "",
      "Afgivet tilbudspris": "10000",
      "Anden aftale om tilbud": "",
      "Tilbud accepteret": "",
      "Accept modtaget - Dato": "",
      "Lovet leveringsdato": "",
      "Faktisk leveringsdato": "",
      "Eltel skyld i forsinkelse": "",
      "Under-entreprenør": "",
      "Telia sagsbehandler": "Keld Lindgaard Nielsen",
      "Eltel projektleder": "Anne Hansted",
      "Øst / Vest": "Vest",
      "Telias sags navn": "211201-Telia-I, Würth Datanet Birkemosevej Kolding",
      "Opgavestatus": "",
      "Dato for status": "",
      "ASSGN": "ASSGN-0040715",
      "Telia RT": "1007333",
      "Telia PO": "4401088790",
      "PO beløb": "10000",
      "PO modtaget - dato": "",
      "Kommentarer + opdateret dato": "",
      "Sag opgjort beløb - total": "20000",
      "Ekstra arbejde efter modtaget design": "5000"
    },
  "@odata.nextLink": "https://flow-apim-europe-001-northeurope-01.azure-apim.net/apim/excelonlinebusiness/shared-excelonlinebu-fc43e90a-8727-4a5a-8718-f0e9e11f6e4c/drives/b!krJ2u4xX7U-hCUoCFvoaoa3RkHaUW1pBr5YSWT6LL4iu4vXAfPTRRpFMS0Wqamvy/files/013R6GQ7GEQCWBBXKTIVALMWWKPIO3KQ7K/tables/%7BE4B0D3C7-30DF-45DE-9367-459CE7D4C1CE%7D/items?source=sites%2feltelgroup.sharepoint.com%2cbb76b292-578c-4fed-a109-4a0216fa1aa1%2c7690d1ad-5b94-415a-af96-12593e8b2f88&%24skip=256"
}

 

This is from "Create item" (SharePoint):

Input:

{
  "Title": "1121496",
  "AO_x0020_status": "Slutfaktureret",
  "Slutfaktura_x002d__x0020_dato": "2017-11-23",
  "Foresp_x00f8_rgsel_x0020_modtage": "",
  "Kolonne1": "",
  "Kolonne2": "",
  "Afgivet_x0020_tilbudspris": "10000",
  "Anden_x0020_aftale_x0020_om_x002": null,
  "Eltel_x0020_skyld_x0020_i_x0020_": "",
  "Under_x002d_entrepren_x00f8_r": "",
  "Telia_x0020_sagsbehandler": "Keld Lindgaard Nielsen",
  "Eltel_x0020_projektleder": "Anne Hansted",
  "OData__x00d8_st_x0020__x002f__x0020_Ve": "Vest",
  "Telias_x0020_sags_x0020_navn": "211201-Telia-I, Würth Datanet Birkemosevej Kolding",
  "Opgavestatus": "",
  "ASSGN": "ASSGN-0040715",
  "Telia_x0020_RT": "1007333",
  "Telia_x0020_PO": "4401088790",
  "PO_x0020_bel_x00f8_b": "10000",
  "Kommentarer_x0020__x002b__x0020_": "",
  "Sag_x0020_opgjort_x00a0_bel_x00f": null,
  "Ekstra_x0020_arbejde_x0020_efter": "5000"
}

Output:

{
  "@odata.etag": "\"1\"",
  "ItemInternalId": "8231",
  "ID": 8231,
  "Title": "1121496",
  "AO_x0020_status": "Slutfaktureret",
  "Slutfaktura_x002d__x0020_dato": "2017-11-23",
  "Afgivet_x0020_tilbudspris": 10000,
  "Telia_x0020_sagsbehandler": "Keld Lindgaard Nielsen",
  "Eltel_x0020_projektleder": "Anne Hansted",
  "OData__x00d8_st_x0020__x002f__x0020_Ve": "Vest",
  "Telias_x0020_sags_x0020_navn": "211201-Telia-I, Würth Datanet Birkemosevej Kolding",
  "ASSGN": "ASSGN-0040715",
  "Telia_x0020_RT": "1007333",
  "Telia_x0020_PO": "4401088790",
  "PO_x0020_bel_x00f8_b": 10000,
  "Ekstra_x0020_arbejde_x0020_efter": 5000,
  "Modified": "2019-11-14T12:54:57Z",
  "Created": "2019-11-14T12:54:57Z",
  "Author": {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
    "Claims": "i:0#.f|membership|andreas.lovschal@eltelnetworks.com",
    "DisplayName": "Andreas Løvschal",
    "Email": "Andreas.Lovschal@eltelnetworks.com",
    "Picture": "https://eltelgroup.sharepoint.com/sites/telia-dk-telia-carrier/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Andreas.Lovschal@eltelnetworks.com",
    "Department": "Telia STD",
    "JobTitle": "Projektleder"
  },
  "Author#Claims": "i:0#.f|membership|andreas.lovschal@eltelnetworks.com",
  "Editor": {
    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
    "Claims": "i:0#.f|membership|andreas.lovschal@eltelnetworks.com",
    "DisplayName": "Andreas Løvschal",
    "Email": "Andreas.Lovschal@eltelnetworks.com",
    "Picture": "https://eltelgroup.sharepoint.com/sites/telia-dk-telia-carrier/_layouts/15/UserPhoto.aspx?Size=L&AccountName=Andreas.Lovschal@eltelnetworks.com",
    "Department": "Telia STD",
    "JobTitle": "Projektleder"
  },
  "Editor#Claims": "i:0#.f|membership|andreas.lovschal@eltelnetworks.com",
  "{Identifier}": "Lists%252fTilbud%2bog%2banlg%2b%2bTest%252f8231_.000",
  "{IsFolder}": false,
  "{Thumbnail}": {
    "Large": null,
    "Medium": null,
    "Small": null
  },
  "{Link}": "https://eltelgroup.sharepoint.com/sites/telia-dk-telia-carrier/_layouts/15/listform.aspx?PageType=4&ListId=f46d3859-175d-4c43-ae45-814b5086a620&ID=8231&ContentTypeID=0x01001F13AD0876F0A54AAA1340DEC0187FE1",
  "{Name}": "1121496",
  "{FilenameWithExtension}": "1121496",
  "{Path}": "Lists/Tilbud og anlg  Test/",
  "{HasAttachments}": false,
  "{VersionNumber}": "1.0"
}

 

I hope that helps, I simply can't figure out what is wrong. 😞

You use addDays multiple times in this step. Are you sure the error comes from

Slutfakturadato

Some other dates seem to be empty strings. Not sure what int() does when the input parameter is an empty string...

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,546)