Excel wrong timestamp format recognition

Question

I am trying to open a CSV file which contains a column named ts which has timestamp in the following pattern throughout the file:

12/31/2016 20:40
mm/dd/yyyy hh:mm

Now when I open this in Excel, some of them are getting recognized some not.

The problem seems to be that Excel is automatically recognising it in dd/mm/yyyy hh:mm format so when a date is 11/13/2016 0:00, it is unable to recognise it(because of 13th Month).

How to fix this?

Answer 1

I've left the Text Import Wizard approach as a separate answer, because it has some useful info. However, I've realized that if we're heading down the path of needing to use a formula, then we might as well just use a formula after opening the file normally!

The following formula works on all date/times that were stored as mm/dd/yyyy hh:mm

=IF(ISNUMBER(A1),DATE(YEAR(A1),DAY(A1),MONTH(A1))+A1-INT(A1),DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2)) + TIMEVALUE(RIGHT(A1,5)))

The formula first checks whether Excel has interpreted the value as a date, which happens when the day is less than or equal to 12. If it has, then Excel has swapped day and month, so the formula swaps them back. Alternatively, if Excel hasn't interpreted the value as a date (which happens when the day is greater than 12), it will still be text and a date/time is generated after rearranging the text string.

Answer 2

You'll find that even when Excel is recognizing dates, it's getting the months and days back to front.

When opening a .csv file directly (e.g. by double clicking it from Windows Explorer or choosing File > Open from within Excel), Excel will try to parse any dates according to your local time format. The only way to change how dates are parsed when opening a file directly is to change your local time format, which you probably don't want to do.

The workaround is to open the file from within Excel using the Text Import Wizard, where you can explicitly state the format in which dates have been stored in the text file.

1. Open a new workbook
2. Go to the Data tab; in the Get External Data group, click From Text
3. Select your file and click Import
4. Step 1: Choose Delimited and select Next
5. Step 2: If it's a true .csv, choose Comma as your only delimiter. (Note that quite often, data exported from other systems is tab-delimited rather than comma separated. However, the .csv file extension is still used to indicate that the file is opened in Excel (or similar). If choosing Comma isn't splitting your columns correctly, try choosing Tab as your delimiter instead. When your data has been split into columns correctly, press Next.)
6. Step 3: Select the column that has dates, then set the Column data format to Date and in the drop down, choose MDY. It should look like this:
7. Click Finish
8. Choose where you want the data to go and click OK

You should now have the data open, with the dates correctly interpreted and also displaying in your local date format (dd/mm/yyyy).

If you want to keep the full date and time in one column, then additional work is required because there's no way of telling Excel to correctly interpret a date/time string that doesn't match your local format.

Start by following the above steps, but at step 6, choose Text as the data format instead. This is necessary to ensure Excel doesn't try to interpret any dates (where day is less than 12).

Then, if your dates are always in mm/dd/yyyy hh:mm format (including leading zeroes for single digit days, months and hours), then the following formula will convert a date/time string that is in cell A1 to a date/time serial that you can format and work with as normal:

=DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2)) + TIMEVALUE(RIGHT(A1,5))

Answer 3

This is happening because the output date you got is in text format, not in date format. Here is the trick to resolve your case to get the output in mm/dd/yyyy hh:mm format. You can change the format to your desired one.

If text date is in A column, then formula is -

=DATE(MID(A3,SEARCH("/",$A3,SEARCH("/",$A3,1)+1)+1,4),LEFT(A3,SEARCH("/",A3,1)-1),MID($A3,SEARCH("/",$A3,1)+1,SEARCH("/",$A3,SEARCH("/",$A3,1)+1)-SEARCH("/",$A3,1)-1))+TIME(HOUR(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,1))),MINUTE(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,1))),SECOND(RIGHT(A3,LEN(A3)-SEARCH(" ",A3,1))))

Hope this helps. Rate if satisfied. :)