Excel Date Formatting and Sorting

Question

I have a data series, where part of it is imported from an external source, and part is input manually. Part of the data includes Date-Time, in the following format "yyyy-mm-dd hh:mm:ss". Although the import/input data looks the same, excel does not appear to recognize the imported data properly, because when I set my computer to time format with AM/PM, the input data displays the AM/PM (in the cell equation) but not the imported data. I changed my computer setting to 24hr, and now all the data visually looks the same, but the data is not sorting/filtering properly.

In the equation bar:

Imported data looks like this: yyyy-mm-dd hh:mm:ss

Input data looks like this (extra space btw dd and hh): yyyy-mm-dd hh:mm:ss

But both visually appear the same in the spreadsheet, but because of the extra space in the input data (I assume) I cannot sort by date as the imported data all sorts newer than the input. Under Formats/Custon, this additional space is not there, but it is present in all of my input data.

If I "edit" an imported cell, (I used CTRL+F2 to enter the cell, then hit enter), it seems to update the formatting and inserts the extra space, and sorting will work properly.

Why is my excel format (yyyy-mm-dd hh:mm:ss) inserting an extra space between the d and h, and is there anything I can do to remove it? I can go through and manually "edit" all the imported cells, but the data set is large, and I don't know if that will affect the ability of the original source to properly read the sheet. I would like to format my input cells to be the same as the imported, but I can't figure out what the issue is.

Thanks

Answer 1

By default, Excel uses 2 spaces between the date and time. Why? I have no idea.

You could use the Replace dialog to replace a single space with two spaces in the imported data. They should then be sorted correctly together with the data that have been entered in Excel itself.