Excel Date Import Problem

[hoody24]

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD="align: right"]41913[/TD]
[/TR]
[TR]
[TD="align: right"]41913[/TD]
[/TR]
[TR]
[TD="align: right"]41913[/TD]
[/TR]
[TR]
[TD="align: right"]41913[/TD]
[/TR]
[TR]
[TD]01/24/2014[/TD]
[/TR]
[TR]
[TD]01/24/2014[/TD]
[/TR]
[TR]
[TD]01/24/2014[/TD]
[/TR]
[TR]
[TD]01/19/2014[/TD]
[/TR]
[TR]
[TD]01/19/2014[/TD]
[/TR]
[TR]
[TD]01/19/2014[/TD]
[/TR]
</tbody>[/TABLE]

Hi there,

I'm importing data into Excel from an external source, in one column I have a series of dates. The problem is that Excel is importing these dates differently - some show up as the serial number so that I can get a 'UK' style dd/mm/yy date from them, others show up as text in the mm/dd/yy US format that I need to work with.

I have tried the text-to-columns trick and countless others but the fact that the data is in different formats is making it very difficult to work with! Can anyone please help?

 

[Joe4]

It sounds to me that you are trying to import dates that exist on a file in the US Style, and your system is defaulting to the UK style.
This can be problematic - check the ones which appear to be working, they may be coming in incorrectly.
For example,

In US style, January 4th of this year looks like this:
01/04/2014
If you try to import that date into a UK style, it WILL convert, but it will incorrectly come in as April 1 instead of January 4.

What is this external file source you are importing from? Note that if it is a text file, you can designate the format of the date by choosing the Date Type, and the appropriate date format (MDY). That should resolve the problem, as you are telling Excel what the format of the date on the incoming file is instead of letting Excel go to its defaults.