A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Convert dates stored as text



 
 
Thread Tools Display Modes
  #1  
Old May 21st 10, 08:59 PM posted to microsoft.public.excel.misc
Emece
external usenet poster
 
Posts: 82
Default Convert dates stored as text

I have Excel 2007 in English, but I sometimes receive data that comes from
another applicationes, so dates are stored as text because they come in
following format: dd/mm/yyyy. And I have the format mm/dd/yyyy.

So, in the same column, I have dates stored as dates, and dates stored as
text. Which is the easiest way to convert them all to date format?

Thanks in advance.

Regards,
Emece.-
Ads
  #2  
Old May 21st 10, 09:53 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default Convert dates stored as text

Be careful.

I'd bet that those values that come in as real dates aren't what the original
data represent.

For instance, if you have two values:
25/12/2010
and
01/02/2010

The first will come in as Text since your windows short date format (under
Regional settings in the windows control panel) is in mdy order.

But the second value can come in as a date. The bad news is that you'll see it
as Jan 2, 2010. But the original data is really dmy order, so it should be Feb
1, 2010.

I wouldn't just convert the text (non-dates) to dates.

I'd use fresh data from the original source.

And you'll have at least couple of options to make sure you get the correct
dates.

The first is to change your windows short date format setting from mdy order to
dmy order. You can make this change right before you import the data, do the
import and change the setting back.

Or you could bring all the data in as text and then parse it the way you like.

If you're doing File|open and opening a CSV file, you could rename the .csv file
to .txt and you'll be prompted with a wizard to parse the records.

Make sure you choose date for the field(s) you need and make sure you match the
same order as the data. You'll be able to reformat the columns to display the
dates the way you like.



Emece wrote:
>
> I have Excel 2007 in English, but I sometimes receive data that comes from
> another applicationes, so dates are stored as text because they come in
> following format: dd/mm/yyyy. And I have the format mm/dd/yyyy.
>
> So, in the same column, I have dates stored as dates, and dates stored as
> text. Which is the easiest way to convert them all to date format?
>
> Thanks in advance.
>
> Regards,
> Emece.-


--

Dave Peterson
  #3  
Old May 22nd 10, 06:37 AM
bala_vb bala_vb is offline
Senior Member
 
First recorded activity by ExcelBanter: Sep 2008
Location: Hyderabad
Posts: 237
Thumbs up

Quote:
Originally Posted by Emece View Post
I have Excel 2007 in English, but I sometimes receive data that comes from
another applicationes, so dates are stored as text because they come in
following format: dd/mm/yyyy. And I have the format mm/dd/yyyy.

So, in the same column, I have dates stored as dates, and dates stored as
text. Which is the easiest way to convert them all to date format?

Thanks in advance.

Regards,
Emece.-
I would recommed to get the data which fetches from other application to seperate column and format to mm/dd/yyyy format, then combine. I think now you will have all the dates in same format

all the best
__________________
Thanks
Bala
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I convert dates stored as dates to text? diamunds Excel Discussion (Misc queries) 5 September 7th 07 05:38 PM
Converting numbers stored as dates to text in Excel David from Furdale Excel Discussion (Misc queries) 1 July 17th 06 06:34 PM
How do I convert nbrs stored as text to nbrs-last version Excel? roouua Excel Discussion (Misc queries) 2 April 17th 06 01:52 PM
Convert numbers stored as text to numbers Excel 2000 Darlene Excel Discussion (Misc queries) 6 January 31st 06 08:04 PM
convert value stored as text to logical refrence value! Remote Desktop Connection hotkey Excel Worksheet Functions 2 August 1st 05 01:56 PM


All times are GMT +1. The time now is 04:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.