Date changes when I click in cell and use Enter key

I am downloading information from a database into XLSX. The source data has date / time fields. Some of the source data reads 12/31/13 11:59 PM.

My end goal is for Excel to show 12/31/13 in those fields.

When I download it, the date / time column formats itself as [$-409]mmm d, yyyy h:mm AM/PM.

The date / time values is no longer 12/31/13 11:59 PM. Something changed...

If I look at the spreadsheet the dates now read Jan 1, 2014 12:00 AM. If I apply any formula or format change to the cells, it always reads 1/1/14, never 12/31/13.

But if I select one of those cells, in the formula bar it actually reads 12/31/2013 12:00:00 AM. (First question: why is this happening?)

Now if my cursor is blinking in the cell and I hit the Enter key, the value in the spreadsheet changes to read Dec 31, 2013 12:00 AM.

NOW if I reformat the cell, it will render as 12/31/13. (Second question: how can I make the cells render correctly as 12/31/13 without having to enter my cursor in thousands of cells and hit the Enter key? And I do not want to use VBA to do it.)

Thanks -- Chris

If I try the multiply by 1 paste trick, of course the format of the affected cells changes to number, and when I change it short date, it reads 1/1/2014 in the spreadsheet and 12/31/2013 12:00:00 AM in the formula bar. Effectively it doesn't change the problem.

The underlying value of the cells is 41639.99999999 in the formula bar, but the cells read that as 41640.00. That never changes. It appears that Excel can't decide whether to round it up by .00000001 (in the cells) or leave it as is (as in the formula bar). If it rounds it up, the date is technically wrong.

So the value doesn't change, it's the format of the cell that's acting squirrelly.

Actually let me qualify one thing: I noticed that if I change the format of the cells to number format, the number will read 41640.00 in the cell and 41639.9999999 in the formula bar...

If I enter my cursor into the formula bar and just hit the Enter key, nothing changes at all. The value stays exactly the same. However...

If I leave all the cells in the date format, and I repeat the above exercise, and THEN change back to the number format, the values have changed. The number in the cell changes to 41639.00, while in the formula bar it changes to 41639 (no decimals).

So the date format of the cell will truncate the value of the cell after I engage the formula bar with the cursor. If I don't, it rounds up to the nearest whole.

There seems to be a glitch in how Excel decides whether to round date / times to the minute.

It also might be worthy to note that if I download the data into CSV the values are exact and correct and do not have an issue, but I can't use CSV due to some other problems with other fields.

Yes, =LEFT(A1,11)*1 does work, also TRUNC(J248) will work.

But I had hoped I wouldn't have to apply formulas to the data. At any rate it may be my only hope.

There you go! That's the magic I was looking for. I wasn't sure how to make the date format more definitive like that. Thanks for your help.