Excel Online to Sharepoint List using PowerAutomate and including Time Values

%3CLINGO-SUB%20id%3D%22lingo-sub-2399596%22%20slang%3D%22en-US%22%3EExcel%20Online%20to%20Sharepoint%20List%20using%20PowerAutomate%20and%20including%20Time%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2399596%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20read%20through%20a%20handful%20of%20posts%20talking%20about%20how%20excel%20and%20powerautomate%20arent%20the%20easiest%20to%20work%20with%20for%20date%20time%20values.%20I%20still%20believe%20I%20can%20set%20up%20this%20flow%2C%20and%20have%20cobbled%20together%20something%20of%20the%20correct%20formulas%20but%20still%20cant%20get%20to%20the%20create%20item%20successfully.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20table%20stored%20in%20a%20sharepoint%20folder%2C%20simple%20event%20list%20with%20a%20start%20date%20and%20time%20column.%3C%2FP%3E%3CP%3Ecurrently%20formatted%20as%20date%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20import%20the%20excel%20data%20into%20a%20sharepoint%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22A_Henriquez_0-1622403966222.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284955iBCD635F763B8F7E9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22A_Henriquez_0-1622403966222.png%22%20alt%3D%22A_Henriquez_0-1622403966222.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22A_Henriquez_1-1622403979587.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284956iAD6F634F76A5D574%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22A_Henriquez_1-1622403979587.png%22%20alt%3D%22A_Henriquez_1-1622403979587.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22A_Henriquez_2-1622403986919.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284957i3F05F1E1A994D01D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22A_Henriquez_2-1622403986919.png%22%20alt%3D%22A_Henriquez_2-1622403986919.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eaddseconds('1899-12-30'%2Cint(formatNumber(mul(variables('VarDate')%2C86400)%2C'0'%2C'en-us'))%2C'M%2Fdd%2Fyyyy%26nbsp%3Bh%3Amm%26nbsp%3Btt')%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22A_Henriquez_3-1622403999000.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284959i1B01F38135F9AA2F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22A_Henriquez_3-1622403999000.png%22%20alt%3D%22A_Henriquez_3-1622403999000.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22A_Henriquez_4-1622404006463.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284960i1B7CADA30E7F5C0F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22A_Henriquez_4-1622404006463.png%22%20alt%3D%22A_Henriquez_4-1622404006463.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EformatDateTime(variables('Event%26nbsp%3BDate')%2C'g')%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20no%20idea%20what%20I%20am%20doing.%20Just%20testing%20various%20things%20out.%20Thank%20you%20for%20any%20advice%20you%20can%20offer.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2399596%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPowerApps%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2401752%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Online%20to%20Sharepoint%20List%20using%20PowerAutomate%20and%20including%20Time%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2401752%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1067068%22%20target%3D%22_blank%22%3E%40A_Henriquez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20just%20wondering%20first%20whether%20there's%20a%20reason%20you're%20not%20able%20to%20use%20SharePoint's%20feature%20to%20create%20a%20list%20from%20an%20Excel%20sheet%3F%20I%20think%20DateTime%20fields%20should%20come%20through%20properly%20in%20that%20scenario%2C%20as%20long%20as%20you%20specify%20the%20column%20as%20DateTime%20during%20the%20list%20creation.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20do%20you%20mean%20the%20list%20already%20exists%20with%20other%20entries%20in%20it%2C%20so%20you're%20trying%20to%20create%20additional%20items%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESandy%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I've read through a handful of posts talking about how excel and powerautomate arent the easiest to work with for date time values. I still believe I can set up this flow, and have cobbled together something of the correct formulas but still cant get to the create item successfully.

 

I have an excel table stored in a sharepoint folder, simple event list with a start date and time column.

currently formatted as date time.

 

I'm trying to import the excel data into a sharepoint list.

 

A_Henriquez_0-1622403966222.png

A_Henriquez_1-1622403979587.png

 

A_Henriquez_2-1622403986919.png

 

addseconds('1899-12-30',int(formatNumber(mul(variables('VarDate'),86400),'0','en-us')),'M/dd/yyyy h:mm tt')

 

A_Henriquez_3-1622403999000.png

 

A_Henriquez_4-1622404006463.png

 

 

formatDateTime(variables('Event Date'),'g')

 

 

I have no idea what I am doing. Just testing various things out. Thank you for any advice you can offer.

2 Replies

Hi @A_Henriquez 

 

I'm just wondering first whether there's a reason you're not able to use SharePoint's feature to create a list from an Excel sheet? I think DateTime fields should come through properly in that scenario, as long as you specify the column as DateTime during the list creation.

 

Or do you mean the list already exists with other entries in it, so you're trying to create additional items?

 

Sandy

@Sandy Ussia 

 

Hi Sandy

 

I have an existing sharepoint list and I want to be able to add in bulk, using excel. 

I just want to set this up to give folks a taste of working in an online environment.

Something as simple as each group saving an excel file to a shared folder, that I can then move into a sharepoint list. The list can then be used for calendars, and possibly planner or project. 

 

I'm also working only with excel online and power automate online - wanting to just roughly test this out. If it works, then the next advice is we seek a consultant for a more comprehensive setup. Especially because all I have figured out so far is how to add, but not actually delete or update existing items.

 

AND - I have actually figured this date/time issue out. I came back here to delete my post - feeling foolish, but I saw your reply. I am so grateful to these communities and others with the most helpful blog and video posts. I followed the method here https://normyoung.ca/2020/07/20/import-excel-data-into-an-existing-sharepoint-list-using-power-autom... and was finally successful. It took me a LONG time to figure out how to apply this in detail - but I finally got it. phew.

 

Thank You for the help,

A Henriquez