Thank you. I am glad that I was *reminded* that text to column can be used to convert text format to date format. You know I have information overload. I can't think of a reason why a formula is needed over custom format and I did it as a practice.

You also teach me the proper terminology - date separator, period etc.

The other day one poster didn't understand what a period was and I was switched to "dot." Afterwards, I forgot to switch back. Thanks for straightening me out.

Always a pleasure chatting with you.

Epinn

"Roger Govier" wrote in message ...
Hi Epinn

Yes, if the date separator is a period, then the entry is a valid Excel
date which can be formatted in any of the various ways of formatting the
date, without needing any additional transformation of the "raw" data.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Roger,

I really like the fact that you are always detailed and I definitely
have benefited a lot.

Just want to say how I interpret 05.01.2007. I see it as a short date
under regional setting for a country in Europe. Out of curiosity, I
went to control panel and experimented. First one I chose was German
(Germany) and I got 08.02.2007 for today. Now I know the German format
uses dot.

I have a feeling that if someone has a "dot" regional setting and key in
08.02.2007, then this is date format and not text format. If this is
true, can we skip the text to column step and go straight to custom
format? Maybe we don't even have to use custom format, but can just
pick one type from the *date* category under formatcellsnumber.

I am not saying that the OP's regional setting is dot. I am just trying
to learn here.

Appreciate your guidance.

Epinn

"Roger Govier" wrote in message
...
Hi

If you are entering text 05.01.07 then you could use the following
Mark the block of cells containing the dates.
DataText to ColumnsNextNextclick the Date radio buttonensure the
dropdown tot he right says DMYFinish
Now Mark the same block of cellsFormatCellsNumberCustom dd.mmm.yy

If you are going to be doing it regularly, you could record a macro
whilst you do it, and then play that back when needed.
--
Regards

Roger Govier


"lady_like" wrote in message
...
hi Chip,

i want to convert the 01 from "05.01.2007" to a text. 01 is month of
January.
so i want to convert it to 05.Jan.2007.


"Chip Pearson" wrote:

If you're looking for a worksheet function, with 05.01.2007 in cell
A1, use

=DATE(RIGHT(A1,4),MID(A1,5,2),LEFT(A1,2))

This formula assumes that month and day number have the leading "0"
(e.g.,
"05" not "5").

If you're looking for a VBA solution, use something like

Dim S As String
Dim Arr As Variant
Dim Dt As Date
S = "05.01.2007"
Arr = Split(S, ".")
Dt = DateSerial(Arr(LBound(Arr) + 2), Arr(LBound(Arr) + 1),
Arr(LBound(Arr)))
Debug.Print Dt



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)





"lady_like" wrote in message
...