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

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

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

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

Roger,

You beat me to it.

I was going to say alternatively, use the custom format dd.mmm.yy.

I am just glad that I thought of it before I read your post, my good teacher.

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

Correction:

I was going to say alternatively, use the custom format dd.mmm.yy. <<

I meant dd.mmm.yyyy.

Epinn

"Epinn" wrote in message ...
Roger,

You beat me to it.

I was going to say alternatively, use the custom format dd.mmm.yy.

I am just glad that I thought of it before I read your post, my good teacher.

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

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

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

hi Roger/Epinn,

thanks for your reply. But i already did a report that my format is
05.01.2007 and my boss wants to retain that format.

my problem is, i can't use the formula that Epinn gave to me because i
still have to convert it 01/05/07. is there a way that i can convert it
directly using this format 05.01.2007?


"Roger Govier" wrote:

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

A1=05.01.2007

If it is date format, then you can use the formula I gave earlier.

If it is text, then try this:

In B1 key in:

=LEFT(A1,3)&TEXT(MID(A1,4,2),"mmm")&RIGHT(A1,5) This is assuming that you always have leading zeros i.e. 05 instead of 5 and 01 instead of 1.

Don't know if Roger is up yet. In the meantime, can you tell us what is your regional setting for date. Go to control panelregional and language optionsregional options What does short date look like?

You are in good hands with Roger.

Epinn

"lady_like" wrote in message ...
hi Roger/Epinn,

thanks for your reply. But i already did a report that my format is
05.01.2007 and my boss wants to retain that format.

my problem is, i can't use the formula that Epinn gave to me because i
still have to convert it 01/05/07. is there a way that i can convert it
directly using this format 05.01.2007?


"Roger Govier" wrote:

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

Hi

Then try
=TEXT(SUBSTITUTE(A1,".","/"),"dd.mmm.yy")

--
Regards

Roger Govier


"lady_like" wrote in message
...
hi Roger/Epinn,

thanks for your reply. But i already did a report that my format is
05.01.2007 and my boss wants to retain that format.

my problem is, i can't use the formula that Epinn gave to me because
i
still have to convert it 01/05/07. is there a way that i can convert
it
directly using this format 05.01.2007?


"Roger Govier" wrote:

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