Date Format.

Mar 13th, 2002, 06:26 AM

I have a list of around 300 dates and other data which was sent me as a text file in the american format (mm/dd/yy). I opened the text file and converted the column as suggested by the prompts. Excel imported them as the american format even though when I looked at the cell format it displayed that it was in dd/mm/yy format. I know that I can swap it around ie mm/dd/yy and this works, but it would be nice if the date was formatted as displayed in the Format>Cell menu.

Any ideas ?

Mark W. · Mar 13th, 2002, 07:35 AM

What's the Date properties setting in your Regional Settings control panel?

Mar 13th, 2002, 07:46 AM

My regional settings in the control panel are 'English (United Kingdom).

Mark W. · Mar 13th, 2002, 07:51 AM

Using =ISTEXT(A1) where A1 contains one of your dates what is returned?

Nigel Martin · Mar 13th, 2002, 08:06 AM

I forgot to login last time but in answer to your question a 'FALSE' statement is returned.

Mark W. · Mar 13th, 2002, 08:21 AM

I trust that you used the Text to Column wizard to import these dates. How did you format them at Step 3 of 3?

Nigel Martin · Mar 13th, 2002, 08:49 AM

When I opened the text file I had the option to format the column as a date field which I did. I don't understand about your text to columns wizard - please explain.

Nigel Martin · Mar 13th, 2002, 08:49 AM

When I opened the text file I had the option to format the column as a date field which I did. I don't understand about your text to columns wizard - please explain.

Mark W. · Mar 13th, 2002, 08:53 AM

Quote:

On 2002-03-13 07:49, Nigel Martin wrote:
When I opened the text file I had the option to format the column as a date field which I did. I don't understand about your text to columns wizard - please explain.

At Step 3 of 3 what date formatting model did you select from the drop down list next to the Date radio button label?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 13th, 2002, 06:26 AM	#1
Guest Posts: n/a	I have a list of around 300 dates and other data which was sent me as a text file in the american format (mm/dd/yy). I opened the text file and converted the column as suggested by the prompts. Excel imported them as the american format even though when I looked at the cell format it displayed that it was in dd/mm/yy format. I know that I can swap it around ie mm/dd/yy and this works, but it would be nice if the date was formatted as displayed in the Format>Cell menu. Any ideas ?

Mar 13th, 2002, 07:35 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	What's the Date properties setting in your Regional Settings control panel?

Mar 13th, 2002, 07:46 AM	#3
Guest Posts: n/a	My regional settings in the control panel are 'English (United Kingdom).

Mar 13th, 2002, 07:51 AM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Using =ISTEXT(A1) where A1 contains one of your dates what is returned?

Mar 13th, 2002, 08:06 AM	#5
Nigel Martin Board Regular Join Date: Feb 2002 Posts: 133	I forgot to login last time but in answer to your question a 'FALSE' statement is returned.

Mar 13th, 2002, 08:21 AM	#6
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	I trust that you used the Text to Column wizard to import these dates. How did you format them at Step 3 of 3?

Mar 13th, 2002, 08:49 AM	#7
Nigel Martin Board Regular Join Date: Feb 2002 Posts: 133	When I opened the text file I had the option to format the column as a date field which I did. I don't understand about your text to columns wizard - please explain.