text to date..!!

V

via135

if i am having a date in a cell as 30/11/2005 but formatted in text,
what's the procedure to change the same to date format as
"30-Mar-2005"?

thks & regds!

via135
 
V

via135

sorry! a small correction!

if i am having a date in a cell as 30/11/2005 but formatted in text,
what's the procedure to change the same to date format as
"30-Nov-2005"?

thks & regds!

via135
 
R

Roger Govier

Hi

One way, with your date in A1, enter in B1
=--A1
Format>Cells>Number>Custom dd=mmm-yyyy

Regards

Roger Govier
 
G

Gord Dibben

Select cell and Data>Text to Columns>Next>Next

Data Format Type>Date DMY Finish.


Gord Dibben Excel MVP
 
P

Peo Sjoblom

One fairly easy solution, select the cell/column with the text dates,
do data>text to column, click next twice and under column data format
select date and from the dropdown select DMY and click finish, this will
change it to a numerical date and then you can just use custom format like

dd-mmm-yyyy
 
P

Peo Sjoblom

Hi Roger,

that would work if the OP is using UK regional settings but not if he uses
US since it will return a value error
 
N

Nikki

if your date is in cell A1 in the next cell type =datevalue(A1), then go to
format-->cell--> date-->choose type 14-Mar-2001 ( it is the last item).

Hope it works.
happy Holidays!
 
P

Peo Sjoblom

This won't work if the OP is using US regional settings which I suspect
he/she might do
 
Top