Converting Text to Date - delimited format ?

**Blewyn** · 12-23-2006, 03:43 AM

I have two columns of dates in the following format :

month/day/year

The months and days are not fixed width, they are delimited by the slash only. For example :

1/2/1999
12/12/1999

are both dates in my database. These fields are text, not date values. When I try to reformat the column as a date, the cells remain text and no operations (such as sorting) can be carried out.

Is there a way of using formulas to convert the dates to actual date values, or must I use Vb ?

Thanks

**VBA Noob** · 12-23-2006, 03:55 AM

Hi,

Did you try format the column to dates ??

If you need a formula try this in B1 and drag down

=DATE(YEAR(A1),MONTH(A1),DAY(A1))

Then format cell as mm/dd/yyyy and copy and paste special values

VBA Noob

**Blewyn** · 12-23-2006, 03:58 AM

Hmm talk about RTFH ! I found the 'FIND' formula......

**Bryan Hessey** · 12-23-2006, 04:24 AM

Originally Posted by Blewyn

Hmm talk about RTFH ! I found the 'FIND' formula......

Hi,

=DATE(RIGHT(A1,4),SUBSTITUTE(IF(LEFT(RIGHT(A1,7),1)="/",LEFT(RIGHT(A1,6),2),LEFT(RIGHT(A1,7),2)),"/",""),LEFT(A1,FIND("/",A1)-1))

would do all combinations for you

hth

amended
---

**arthurbr** · 12-23-2006, 07:16 AM

Enter "1" in a cell. Copy the content of this cell.
Select the range of dates you want to change and select Paste Speclal - Multiply - OK.
All the cells will be converted to dates , you must then format as you wish.

**oldchippy** · 12-23-2006, 11:06 AM

Originally Posted by arthurbr

Enter "1" in a cell. Copy the content of this cell.
Select the range of dates you want to change and select Paste Speclal - Multiply - OK.
All the cells will be converted to dates , you must then format as you wish.

That method doesn't work if the text date is say 10/21/1999

**Bryan Hessey** · 12-23-2006, 12:25 PM

Originally Posted by oldchippy

That method doesn't work if the text date is say 10/21/1999

I don't like to trust Excel with dates, I prefer to specify whereever possible, especially considering the mess it makes of trying to export to csv a non-US format date.

---

**daddylonglegs** · 12-23-2006, 01:07 PM

Originally Posted by Blewyn

Is there a way of using formulas to convert the dates to actual date values, or must I use Vb ?

You can covert in situ without formulas or VB

Select the column of dates

Data > text to columns > Next > Next

At step 3 select "Date" from "Column data format" and "MDY" from dropdown

Finish

**Bryan Hessey** · 12-23-2006, 01:21 PM

Originally Posted by daddylonglegs

You can covert in situ without formulas or VB

Select the column of dates

Data > text to columns > Next > Next

At step 3 select "Date" from "Column data format" and "MDY" from dropdown

Finish

Yes, but that was after Excel '97 (for those still using that)

In '97 the format was taken from the first date in the column.

hth
---

**Blewyn** · 12-24-2006, 07:39 AM

I did it by using TEXT to convert all dates to text (I had a mixture of date and text fields in the same column), then "Text to Columns" to separate out the values for day, month and year,then used DATE(Year,Month,Day) to convert back to a proper date value.

Thanks to all for your advice, and Merry Christmas !

**VBA Noob** · 12-24-2006, 07:50 AM

Blewyn,

Glad you got an answer.

Merry Xmas

VBA Noob

Converting Text to Date - delimited format ?

LinkBack

Thread Tools

Rate This Thread

Display

Converting Text to Date - delimited format ?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions