Import dates from TXT

[nathan86]

Hi

I've got a question about excel

I've got a notepad document in TXT format
with some information about names and dates

I like to import the list in excel
now this is my file for example

Mikey 10-12-1960
Sandy 15-11-1984

This is the file i'd like to import

But when I import it in Excel the dates are not standard reconized as
dates
How can I make Excel detect that it are dates?

How can I make excel make the cel where it places the date
automatic transform into a date cell without manually have to change
cel properties every time I import TXT files including dates.

Thanks,

Nathan

 

[Guest]

Hi

One way, after import, is to use Data/Text to columns. Make sure there is an
empty column to the right of your data. Select you column of 'dates' and go
to Data/Text to columns. Click Next, and Next and on the third dialog box
you can select 'Date' in the top right corner - and also the format. Job
done!

Andy.

 

[nathan86]

The Idea is that this goes automated

It's a program that compiles the text file automatic and also opens it
in excel automatic

I want excel to automatic reconize dates from the text file and there
by give it the parameter date because it's a date

not because i manually make it a date
Or first have to convert my data

How can i just import a text file with dates without letting excel know
it are dates it has to figure that out for itself.

Thanks for the quick reply b.t.w.

 

[Dave Peterson]

Is your file named *.txt?

If it is, then you should see the text import wizard after you do File|Open.

You can define the input (delimited or fixed width), but when you get to the
field that represents those dates, make sure you choose Date and dmy as the
format.

Excel should bring it in correctly (based on your two examples).

 

[nathan86]

I found out this weird thing

Maybe you can test it

Office 2000

When I type a date in an empty cel in an empty sheet

10-12-2006

this is the 10th of December 2006

When I set the cel properties to the following date format

dd-mm-jjjj

noted as 14-3-1945 or something

In the cel the following date appears

12-10-2006

but when after that I change the format to 14-mrt-1945

dd-MMMM-jjjj

it changes 12-10-2006 to 10 December
How is this possible??????

 

[Dave Peterson]

This is a bad day to check (06/06/06 is way to ambiguous a date).

But if you hit ctrl-; (control-semicolon) in an empty cell, what do you get.

That's the order I'd enter dates. I think you'll want to match the order (dmy
or mdy or whatever) that your windows settings (control panel|regional settings)
shows.

 

[nathan86]

Ok

When I use CTRL-;
I get

7-6-2006

But when look at the cell properties it's set to

aangepast (adapted) (my excel is dutch so it might be a littl
different)
and then in the right column at type
it says d-m-jjjj

But when I convert the cell type to date
and choose the 14-3-1998 format

it displays it like 6-7-2006 instead of 7-6-2006
but when i look at the formula bar it's displayed as 7-6-2006

Why is excel messing up my date format from
d-m-jjjj to m-d-jjjj

Now comes the beautyfull part

when I click the cell properties of the converted cell and i change th

month display to text like 14-mrt-1998

It displays 07-jun-2006 in the cell

Why does it display 6-7-2006 instead of 7-6-2006
when the date format for windows as for excel are both d-m-jjjj

but when month is converted to text it turns it back to the correc
order.

So weird

Thanks for your attention

Nathan van Haaste

 

[Dave Peterson]

I don't have a guess.

Maybe someone else will know???

What was the short date format in your windows regional settings?

 

[nathan86]

MY SHORT DATE FORMAT IS
d-m-yyyy


MY LONG DATE FORMAT IS
dddd d MMMM yyyy

It seems like a bug in excel cause no matter what I do
it keeps twisting the date.

Changing date format in windows doesn't affect my results.

I tested on serveral machines and every time I had the same problem

When you follow up what I did do you get the same problem or is it jus
on my pc / office version

type a date in an empty cell

convert the cell to

Type : Date
Format : 14-3-1998

It switches day with month
but when you convert month to text it displays correct

Realy weird

But thanks for the quick reply's and help you offere

 

[Dave Peterson]

For me, the value shown in the formula bar matches the windows short date
format.

I'm still out of guesses for you.

 

[nathan86]

I was wondering

Where you able to replicate my problem

In American Date format it would function fine but in european i
doesn't

type in an empty cel 10-12-1998 when date format in windows is set to
dd-mm-yyyy

convert the cell to dd-mm-yyyy in excel and make it a date.

Right click the cell
Properties
Date
its viewed as 14-3-1998

you will see the date go from 10-12-1998 to 12-10-1998
but when month is converted to text it does state it as 10th of Dec

But in european date format after converting the cel it displays a
12th of okt

Let me know if you where able to replicate my problem\

Thnk

 

[Dave Peterson]

Leaving the cell formatted as General, I type the date in the same order as my
regional settings.

Excel sees it as a date and keeps that same order as the regional settings.

For instance:

My windows regional settings are USA: mm/dd/yyyy (I like leading 0's).

I type 6/14/2006 and I see 06/14/2006 (the date of June 14, 2006) in the formula
bar.

If I close excel and change my regional settings to dd/mm/yyyy.

Then reopen excel and type 14/6/2006 (to match my regional settings format),
then 14/06/2006 is seen in the formula bar.

As long as the cell is formatted as general to start, I see the same thing in
the cell as the formula bar.

If I change the format of the cell to be different from the windows regional
setting, then I may see something different--but that's not a change in
value--it's a change in the way the value is displayed in the cell. (The value
in the formula bar doesn't change.)