SOLVED

Unable to create a new Teams List from Excel using date formatted columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1950316%22%20slang%3D%22en-US%22%3EUnable%20to%20create%20a%20new%20Teams%20List%20from%20Excel%20using%20date%20formatted%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1950316%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20creating%20a%20Teams%20List%20from%20an%20Excel%20spreadsheet%20that%20contains%20columns%20with%20date%20formats%2C%20the%20columns%20with%20dates%20are%20converted%20to%20their%20numeric%20value%20and%20there%20is%20no%20way%20to%20adjust%20the%20column%20format%20to%20Date%20and%20Time%20format.%3C%2FP%3E%3CP%3EThis%20occurs%20with%20multiple%20users%2C%20both%20desktop%20and%20web%20clients.%26nbsp%3B%20We%20do%20have%20ability%20to%20create%20from%20scratch%20new%20lists%20with%20date%20columns.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1950316%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMicrosoft%20Teams%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1952109%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20create%20a%20new%20Teams%20List%20from%20Excel%20using%20date%20formatted%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1952109%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F440509%22%20target%3D%22_blank%22%3E%40LaurieBeamish%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreating%20list%20from%20Excel%20file%20in%20Lists%20app%2C%20both%20in%20Teams%20and%20in%20O365%2C%20you%20have%20an%20option%20to%20change%20the%20type%20of%20the%20column.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20682px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237509i8DD80AC73801D32B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20missed%20that%20when%20dates%20will%20be%20shown%20as%20numbers.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDates%20format%20here%20as%20for%20Sharepoint%20site%20(Teams)%20or%20as%20in%20your%20O365%20profile%20(Lists%20app).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1952668%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20create%20a%20new%20Teams%20List%20from%20Excel%20using%20date%20formatted%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1952668%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Sergei.%26nbsp%3B%20That%20was%20somewhat%20invisible%20to%20me%20until%20you%20pointed%20that%20out.%26nbsp%3B%20That%20got%20me%20one%20step%20further%20but%20have%20run%20into%20another%20issue.%26nbsp%3B%20When%20I%20change%20the%20number%20to%20Date%20and%20Time%20I%20now%20get%20a%20new%20error%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22LaurieBeamish_0-1607002797900.png%22%20style%3D%22width%3A%20670px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237517iE3EE14414B7950FE%2Fimage-dimensions%2F670x34%3Fv%3D1.0%22%20width%3D%22670%22%20height%3D%2234%22%20role%3D%22button%22%20title%3D%22LaurieBeamish_0-1607002797900.png%22%20alt%3D%22LaurieBeamish_0-1607002797900.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20went%20back%20into%20the%20Excel%20spreadsheet%20and%20changed%20the%20dates%20to%20the%20%23%23%2F%23%23%2F%23%23%20%23%3A%23%23%20PM%20format.%26nbsp%3B%20Saved%20the%20file%20and%20tried%20again%20and%20got%20the%20same%20error.%26nbsp%3B%20I%20then%20went%20back%20into%20the%20Excel%20and%20changed%20the%20Format%20to%20Custom%20m%2Fd%2Fyyyy%20h%3Amm%20AM%2FPM%20since%20the%20requested%20format%20was%20not%20available.%20That%20worked%20with%20one%20column%20but%20when%20I%20tried%20with%20other%20date%20columns%20got%20the%20error%20again.%26nbsp%3B%20Very%20kluge%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

When creating a Teams List from an Excel spreadsheet that contains columns with date formats, the columns with dates are converted to their numeric value and there is no way to adjust the column format to Date and Time format.

This occurs with multiple users, both desktop and web clients.  We do have ability to create from scratch new lists with date columns.  

 

5 Replies
best response confirmed by LaurieBeamish (Occasional Contributor)
Solution

@LaurieBeamish 

Creating list from Excel file in Lists app, both in Teams and in O365, you have an option to change the type of the column.

image.png

If you missed that when dates will be shown as numbers. 

Dates format here as for Sharepoint site (Teams) or as in your O365 profile (Lists app).

@Sergei Baklan 

Thanks Sergei.  That was somewhat invisible to me until you pointed that out.  That got me one step further but have run into another issue.  When I change the number to Date and Time I now get a new error:

LaurieBeamish_0-1607002797900.png

I went back into the Excel spreadsheet and changed the dates to the ##/##/## #:## PM format.  Saved the file and tried again and got the same error.  I then went back into the Excel and changed the Format to Custom m/d/yyyy h:mm AM/PM since the requested format was not available. That worked with one column but when I tried with other date columns got the error again.  Very kluge  

 
 

 

 

 

 

 

 

 

 

@LaurieBeamish 

Sorry to hear that, but I can't help with this, did only couple of lists to test functionality.

@Sergei Baklan
I really appreciate your pointing out the place to change the formatting. Web documentation doesn't go as far as you did.

@LaurieBeamish , glad to help, good luck with Lists!