Hi,

While you wait for Chip, I can share with you what I did.

I am on a different date system. My regional setting (via control panel) is m-d-yyyy.

In A1 (general format): I key in 1/5/07 and it displays 1/5/2007 where 1 = month i.e. Jan.

In B1: =TEXT(DAY(A1),"00")&"."&TEXT(MONTH(A1),"mmm")&"."& YEAR(A1)

This formula returns 05.Jan.2007

Hope this helps.

Epinn

"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
...