Date format problems

%3CLINGO-SUB%20id%3D%22lingo-sub-1355294%22%20slang%3D%22en-US%22%3EDate%20format%20problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1355294%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20data%20set%20output%20from%20a%20blood%20sugar%20test%20meter%20(Bayer%20Contour%20Next%20USB)%3C%2FP%3E%3CP%3E1020%20rows%2C%2019%20columns.%20Imported%20as%20CSV.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20sort%20the%20data%20by%20the%20date%20column%20but%20from%20row%20400%20onward%20it%20will%20not%20sort%20by%20date%20order.%20I%20have%20tried%20altering%20the%20date%20format%20of%20the%20column%2C%20but%20only%20rows%201%20to%20399%20will%20do%20change%20format%2C%20the%20rest%20stay%20as%20they%20were%20and%20the%20sort%20function%20does%20not%20recognise%20the%20rows%20as%20dates%20and%20will%20not%20sort%20them.%3C%2FP%3E%3CP%3EI%20have%20tried%20saving%20as%20.xlsx.%20Same%20problems.%3C%2FP%3E%3CP%3EAny%20ideas%20anyone%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1355294%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1355318%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1355318%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F645377%22%20target%3D%22_blank%22%3E%40Stewart_H%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20since%20some%20%22dates%22%20are%20kept%20as%20text%2C%20some%20are%20converted%20wrongly.%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%20144px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188423i5DF9EDF15C8DD792%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EImporting%20csv%20file%20do%20not%20rely%20on%20automatic%20conversion.%20If%20that's%20legacy%20wizard%2C%20on%20third%20step%20apply%20Date%20to%20this%20column%20with%20MDY%20format.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1355802%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1355802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F645377%22%20target%3D%22_blank%22%3E%40Stewart_H%3C%2FA%3E%26nbsp%3Bsome%20date%20in%20date%20column%20is%20import%20as%20text%20for%20sorting%20all%20the%20date%20it%20is%20essential%20that%20all%20are%20in%20date%20format%20i%20used%20text%20to%20column%20in%20data%20tab%20to%20convert%20all%20your%20date%20in%20date%20format%20i%20have%20attached%20a%20solution%20file%20check%20it%2C%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1355835%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1355835%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F639126%22%20target%3D%22_blank%22%3E%40Khizar_Hayyat%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20not%20sure%20that%20is%20correct%20result.%20Let%20take%20these%20two%20records%20in%20initial%20file%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%20143px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188455i60821D2165D83B2D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EMost%20probably%20in%20%2354%20in%20source%20csv%20file%20it%20was%2011%2F08%2F2019.%20Since%20both%2011%20and%2008%20are%20less%20than%2012%2C%20Excel%20was%20interpreted%20%2454%20as%20date%20in%20local%20format%20and%20converted%20to%20Aug%2011.%20Text%20to%20Columns%20on%20result%20keeps%20converted%20date%20as%20it%20is%2C%20thus%20we%20have%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%20160px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188456i7F052E1C2B0F2767%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EMost%20probably%20in%20%2354%20it%20shall%20be%20Nov%2008%2C%20or%202019-11-08%20in%20ISO%20format.%20Same%20for%20some%20other%20records.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThus%20I'd%20suggest%20to%20convert%20properly%20source%20csv%20file%2C%20not%20to%20transform%20intermediate%20files.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1355985%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1355985%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%3EYes%2C%20that%20is%20the%20issue.%20Some%20of%20the%20text%20will%20not%20reformat%20to%20date.%3C%2FP%3E%3CP%3EThe%20CSV%20file%20is%20created%20by%20the%20software%20on%20the%20blood%20test%20meter%20which%20is%20not%20accesible%20by%20me.%20Can%20I%20control%20how%20excel%20converts%20it%20when%20I%20open%20the%20CSV%20file%3F%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F639126%22%20target%3D%22_blank%22%3E%40Khizar_Hayyat%3C%2FA%3E%26nbsp%3Bhas%20managed%20to%20convert%20all%20cell%20to%20date%20format%20but%20day%20and%20month%20have%20been%20mixed%20up.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F639126%22%20target%3D%22_blank%22%3E%40Khizar_Hayyat%3C%2FA%3E%26nbsp%3BHow%20did%20you%20convert%20all%20to%20date%20format%3F%26nbsp%3B%20Simple%20explaination%20for%20excel%20novice%20please.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356006%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F645377%22%20target%3D%22_blank%22%3E%40Stewart_H%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20better%20not%20to%20click%20on%20csv%20file%20to%20open%20it%2C%20but%20import%20csv%20into%20the%20blank%20Excel%20file.%20How%20to%20do%20-%20it%20depends%20on%20which%20version%20of%20Excel%20you%20are.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAssuming%20one%20of%20the%20latest%20for%20Windows%20Desktop.%20Here%20is%20also%20two%20options.%20You%20may%20enable%20legacy%20%3CSTRONG%3EFrom%20text%3C%2FSTRONG%3E%20connector%20in%20options%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%20651px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188486i0C48A3E037546A3F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20use%20it%20as%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%20495px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188487i6C23EE32311D514B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Efor%20older%20Excel%20that's%20the%20only%20option%20by%20default.%3C%2FP%3E%0A%3CP%3EAfter%20you%20select%20the%20file%20it'll%20be%203-steps%20wizard%2C%20practically%20same%20as%20for%20Data-%26gt%3BText%20to%20Columns%20which%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F639126%22%20target%3D%22_blank%22%3E%40Khizar_Hayyat%3C%2FA%3E%26nbsp%3B%20used.%20Here%20select%20separation%20by%20delimiter%2C%20your%20delimiter%2C%20and%20on%203rd%20step%20of%20the%20wizard%20check%20Date%20for%20the%20column%20with%20dates%20and%20select%20MDY%20format.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20way%20is%20default%20for%20the%20latest%20Excel%20option%20uses%20Power%20Query%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%20479px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188488iEB9ED0C81FA7ACBC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhich%20is%20much%20more%20powerful%20and%20most%20probably%20will%20give%20you%20correct%20result%20without%20additional%20manipulations%2C%20but%20it%20requires%20to%20invest%20bit%20more%20time%20to%20start%20working%20with%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356436%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356436%22%20slang%3D%22en-US%22%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%20your%20solution%20is%20too%20powerful%20but%20it%20takes%20a%20lot%20of%20time%20but%20i%20am%20sure%20this%20is%20the%20right%20way%20to%20done%20it%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1356483%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1356483%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F645377%22%20target%3D%22_blank%22%3E%40Stewart_H%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot_11.jpg%22%20style%3D%22width%3A%20518px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188535iD1F45F486E1905BB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot_11.jpg%22%20alt%3D%22step%202%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Estep%202%3C%2FSPAN%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%22Screenshot_12.jpg%22%20style%3D%22width%3A%20518px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188536i380B9C2C2A146DE3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot_12.jpg%22%20alt%3D%22step%203%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Estep%203%3C%2FSPAN%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%22Screenshot_13.jpg%22%20style%3D%22width%3A%20524px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188537i94EF1CABB08269E1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot_13.jpg%22%20alt%3D%22Screenshot_13.jpg%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%22Screenshot_14.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188538i5CD22E0EF27F7CBB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screenshot_14.jpg%22%20alt%3D%22note%3A%20be%20carefully%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3Enote%3A%20be%20carefully%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehope%20that%20you%20will%20find%20it%20good%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am working on a data set output from a blood sugar test meter (Bayer Contour Next USB)

1020 rows, 19 columns. Imported as CSV. 

I want to sort the data by the date column but from row 400 onward it will not sort by date order. I have tried altering the date format of the column, but only rows 1 to 399 will do change format, the rest stay as they were and the sort function does not recognise the rows as dates and will not sort them.

I have tried saving as .xlsx. Same problems.

Any ideas anyone?

7 Replies
Highlighted

@Stewart_H 

That's since some "dates" are kept as text, some are converted wrongly.

image.png

Importing csv file do not rely on automatic conversion. If that's legacy wizard, on third step apply Date to this column with MDY format. 

Highlighted

@Stewart_H some date in date column is import as text for sorting all the date it is essential that all are in date format i used text to column in data tab to convert all your date in date format i have attached a solution file check it, 

Highlighted

@Khizar_Hayyat 

I'm not sure that is correct result. Let take these two records in initial file

image.png

Most probably in #54 in source csv file it was 11/08/2019. Since both 11 and 08 are less than 12, Excel was interpreted $54 as date in local format and converted to Aug 11. Text to Columns on result keeps converted date as it is, thus we have

image.png

Most probably in #54 it shall be Nov 08, or 2019-11-08 in ISO format. Same for some other records.

 

Thus I'd suggest to convert properly source csv file, not to transform intermediate files.

Highlighted

@Sergei Baklan 

Yes, that is the issue. Some of the text will not reformat to date.

The CSV file is created by the software on the blood test meter which is not accesible by me. Can I control how excel converts it when I open the CSV file?

@Khizar_Hayyat has managed to convert all cell to date format but day and month have been mixed up.

@Khizar_Hayyat How did you convert all to date format?  Simple explaination for excel novice please.

 

Highlighted

@Stewart_H 

It's better not to click on csv file to open it, but import csv into the blank Excel file. How to do - it depends on which version of Excel you are.

 

Assuming one of the latest for Windows Desktop. Here is also two options. You may enable legacy From text connector in options

image.png

and use it as

image.png

for older Excel that's the only option by default.

After you select the file it'll be 3-steps wizard, practically same as for Data->Text to Columns which @Khizar_Hayyat  used. Here select separation by delimiter, your delimiter, and on 3rd step of the wizard check Date for the column with dates and select MDY format.

 

Another way is default for the latest Excel option uses Power Query

image.png

which is much more powerful and most probably will give you correct result without additional manipulations, but it requires to invest bit more time to start working with it.

Highlighted
@Sergei Baklan your solution is too powerful but it takes a lot of time but i am sure this is the right way to done it
Highlighted

 

@Stewart_H 

step 2step 2

 

step 3step 3

 

Screenshot_13.jpg

 

note: be carefullynote: be carefully

 

hope that you will find it good