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

[Anonymous]

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:

[DominikPetri]

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]

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]

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

[v-litu-msft]

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:

I just tested your expression and it is no problem:

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]

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:

[v-litu-msft]

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]

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. 😞

[DominikPetri]

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