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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (5,534)