Importing dates from Excel into Sharepoint list

%3CLINGO-SUB%20id%3D%22lingo-sub-1614886%22%20slang%3D%22en-US%22%3EImporting%20dates%20from%20Excel%20into%20Sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614886%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI've%20tried%20various%20forums%20for%20answers%20to%20this%20issue%20but%20none%20have%20succeeded.%20I%20have%20an%20extensive%20spreadsheet%20that%20I%20want%20to%20import%20a%20Sharepoint%20list.%20The%20issue%20is%20the%20date%20column.%20irrespective%20of%20how%20I%20format%20the%20date%20in%20Excel%20it%20does%20not%20conform%20to%20the%20date%2Ftime%20format%20in%20Sharepoint.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TSimsD_0-1598486845267.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214889i94AC1554A63502F7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TSimsD_0-1598486845267.png%22%20alt%3D%22TSimsD_0-1598486845267.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20error%20message%20(note%20the%20error%20varies%20depending%20on%20how%20I%20format%20in%20excel)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TSimsD_1-1598486944185.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214890iE24EA4BE3758BC37%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TSimsD_1-1598486944185.png%22%20alt%3D%22TSimsD_1-1598486944185.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20am%20sure%20there%20should%20be%20a%20simple%20function%20fix%20but%20I%20suspect%20I%20don't%20have%20sufficient%20knowledge%20to%20understand%20potential%20solutions%20offered%20on%20other%20forums.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eany%20help%2Fadvice%20gratefully%20received.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1614886%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Lists%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1614896%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20dates%20from%20Excel%20into%20Sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1614896%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F772594%22%20target%3D%22_blank%22%3E%40TSimsD%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20the%20example%20shown%20is%20a%20test%20file%20I'm%20using%20as%20the%20working%20sheet%20is%20too%20large%20to%20easily%20manipulate%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1615945%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20dates%20from%20Excel%20into%20Sharepoint%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1615945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F772594%22%20target%3D%22_blank%22%3E%40TSimsD%3C%2FA%3E%26nbsp%3BExcel%20stores%20dates%20as%20an%20integer%20of%20the%20number%20of%20days%20since%2030%20December%201899.%20So%20you%20need%20to%20convert%20that%20integer%20to%20a%20date%20before%20your%20flow%20can%20use%20the%20output%20in%20SharePoint.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20you%20will%20need%20to%20add%20Compose%20control.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20in%20my%20spreadsheet%20has%20a%20column%20called%20Time%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22ccCheck.png%22%20style%3D%22width%3A%20666px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214998iBDEEE2110FB33C5E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22ccCheck.png%22%20alt%3D%22ccCheck.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20flow%20you'll%20need%20to%20convert%20that%20column%20using%20an%20expression%20in%20a%20Compose%20control%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EformatDateTime(addDays('1900-01-01'%2C%20add(int(items('Apply_to_each')%3F%5B'%3CSTRONG%3ETime%3C%2FSTRONG%3E'%5D)%2C-2))%2C%20'yyyy-MM-dd')%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E(the%20way%20I%20do%20it%20is%20to%20use%201900-01-01%20for%20ease%20of%20remembering%20rather%20than%201899-12-30%2C%20then%20take%20off%202%20days%20from%20the%20result)%3C%2FEM%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22excelDateFormat.png%22%20style%3D%22width%3A%20874px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214993iDEAB061C53BDCCF0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22excelDateFormat.png%22%20alt%3D%22excelDateFormat.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20then%20use%20the%20output%20of%20that%20Compose%20in%20the%20relevant%20field%20in%20your%20SharePoint%20Create%20item%20action.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User.%3CBR%20%2F%3EIf%20I've%20answered%20your%20question%20or%20solved%20your%20problem%2C%20please%20mark%20this%20question%20as%20answered.%20This%20helps%20others%20who%20have%20the%20same%20question%20find%20a%20solution%20quickly%20via%20the%20forum%20search.%20If%20you%20liked%20my%20response%2C%20please%20consider%20giving%20it%20a%20thumbs%20up.%20Thanks.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

I've tried various forums for answers to this issue but none have succeeded. I have an extensive spreadsheet that I want to import a Sharepoint list. The issue is the date column. irrespective of how I format the date in Excel it does not conform to the date/time format in Sharepoint.

TSimsD_0-1598486845267.png

The error message (note the error varies depending on how I format in excel)

TSimsD_1-1598486944185.png

I am sure there should be a simple function fix but I suspect I don't have sufficient knowledge to understand potential solutions offered on other forums.

 

any help/advice gratefully received.

 

2 Replies
Highlighted

@TSimsD 

Note the example shown is a test file I'm using as the working sheet is too large to easily manipulate

Highlighted

@TSimsD Excel stores dates as an integer of the number of days since 30 December 1899. So you need to convert that integer to a date before your flow can use the output in SharePoint.

 

So you will need to add Compose control.

 

The table in my spreadsheet has a column called Time:

ccCheck.png

 

In your flow you'll need to convert that column using an expression in a Compose control:


formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['Time']),-2)), 'yyyy-MM-dd')

 

(the way I do it is to use 1900-01-01 for ease of remembering rather than 1899-12-30, then take off 2 days from the result)

excelDateFormat.png

 

You then use the output of that Compose in the relevant field in your SharePoint Create item action.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.