SOLVED

need help with dates

%3CLINGO-SUB%20id%3D%22lingo-sub-3301059%22%20slang%3D%22en-US%22%3Eneed%20help%20with%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301059%22%20slang%3D%22en-US%22%3E%3CP%3ESome%20dates%20in%20DOB%20column%20are%20in%20text%20format%20and%20text-to-colums%20is%20not%20working.%20How%20to%20convert%20these%20into%20date%20format%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Screenshot%20(54).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F368753iB4D4F0EBBCCC41B3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(54).png%22%20alt%3D%22Screenshot%20(54).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3301059%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301090%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301090%22%20slang%3D%22en-US%22%3Ethanks%20man%20it%20worked%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301062%22%20slang%3D%22en-US%22%3ERe%3A%20need%20help%20with%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301062%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1379378%22%20target%3D%22_blank%22%3E%40shergill_13%3C%2FA%3E%26nbsp%3BI%20suspect%20your%20system%20recognises%20dates%20in%20the%20mm-dd-yyyy%20format.%20So%2C%20TTC%20fails%20when%20it%20sees%20a%20date%20like%2013-1-1970%2C%20as%20there%20is%20no%20month%2013.%20Make%20sure%20that%20in%20step%203%20of%203%2C%20you%20set%20the%20date%20format%20of%20the%20text%20to%20be%20imported%20as%20DMY.%20Then%20the%20dates%20will%20transformed%20to%20your%20local%20(default)%20date%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Some dates in DOB column are in text format and text-to-colums is not working. How to convert these into date format

Screenshot (54).png

 

2 Replies
best response confirmed by shergill_13 (New Contributor)
Solution

@shergill_13 I suspect your system recognises dates in the mm-dd-yyyy format. So, TTC fails when it sees a date like 13-1-1970, as there is no month 13. Make sure that in step 3 of 3, you set the date format of the text to be imported as DMY. Then the dates will transformed to your local (default) date format.

 

 

thanks man it worked