Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
|
|||
|
|||
Quote:
all the best
__________________
Thanks Bala |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert dates stored as dates to text? | Excel Discussion (Misc queries) | |||
Converting numbers stored as dates to text in Excel | Excel Discussion (Misc queries) | |||
How do I convert nbrs stored as text to nbrs-last version Excel? | Excel Discussion (Misc queries) | |||
Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
convert value stored as text to logical refrence value! | Excel Worksheet Functions |