how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

Sorry, my second formula only works for January. I am fixing it now.

Epinn

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

Hi

I should have added, this will give a Text result.
If you want the result to be a true Excel date for use in further
calculations, then use
=--(SUBSTITUTE(A1,".","/"))
and then FormatCellsNumberCustom dd.mmm.yy

--
Regards

Roger Govier


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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

Roger,

Will you forgive me. I am going backwards and I'll make a point to shut up. I learned all the fancy formulae to sort text, numbers, number and text etc. and I have forgotten something as simple as TEXT and DATE.

I am going to post the correct formula for the record. Of course, we all should use your simple solutions.

A1: 01.03.2007 text format dd.mm.yyyy

B1: =TEXT(DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)),"dd .mmm.yyyy") returns 01.Mar.2007 (text format).

Must have leading 0 in A1 for this formula to work. e.g. 01 and not just 1, 03 and not just 3. 1.3.2007 won't work.

This formula is *independent* of my regional setting.

If I use the SUBSTITUTE "/" method, then the dd.mm.yyyy format in A1 will be over-ridden by my regional setting which is m/d/yyyy i.e. I will get Jan instead of Mar.

Please ignore my very first formula. Don't mean to confuse anyone. My apology to the OP. Now I have to go and find something to tie up my fingers to prevent further typing unless critical.

Epinn

"Epinn" wrote in message ...
Sorry, my second formula only works for January. I am fixing it now.

Epinn

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

I am typing with one hand. I have to say something important.

If I use the SUBSTITUTE "/" method, then the dd.mm.yyyy format in A1 will be over-ridden by my regional setting which is m/d/yyyy i.e. I will get Jan instead of Mar.<<

Text to column + custom format (dd.mmm.yyyy) is perfect for me. I say this method is universal i.e. independent of the regional setting.

Substitute formula + custom format may work for some but not all users i.e. dependent of regional setting. If it works on the OP's PC, then what will happen if he/she distributes the worksheet to a user with a different regional setting? Not sure if [$-409] is of any help. No clue what it is.

Okay, I said it.

Epinn

"Epinn" wrote in message ...
Roger,

Will you forgive me. I am going backwards and I'll make a point to shut up. I learned all the fancy formulae to sort text, numbers, number and text etc. and I have forgotten something as simple as TEXT and DATE.

I am going to post the correct formula for the record. Of course, we all should use your simple solutions.

A1: 01.03.2007 text format dd.mm.yyyy

B1: =TEXT(DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)),"dd .mmm.yyyy") returns 01.Mar.2007 (text format).

Must have leading 0 in A1 for this formula to work. e.g. 01 and not just 1, 03 and not just 3. 1.3.2007 won't work.

This formula is *independent* of my regional setting.

If I use the SUBSTITUTE "/" method, then the dd.mm.yyyy format in A1 will be over-ridden by my regional setting which is m/d/yyyy i.e. I will get Jan instead of Mar.

Please ignore my very first formula. Don't mean to confuse anyone. My apology to the OP. Now I have to go and find something to tie up my fingers to prevent further typing unless critical.

Epinn

"Epinn" wrote in message ...
Sorry, my second formula only works for January. I am fixing it now.

Epinn

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

how will i convert 05.01.2007 convert 05.Jan.2007 format?
 

Hi Epinn

You need to invest in a straightjacket <vbg

I hear what you say about Regional settings, but as the OP had said she
used 05.01.07 and wanted 05.Jan.07 I inferred that her Regional settings
were the same as my UK settings of dd/mm/yy.

For you, I presume you would not enter as 05.01.07, but would enter as
01.05.07 if you were entering a date of 05 January 07, and in which case
the result would turn out as 01.Jan.07 having used the formula.

If one is going to distribute Workbooks internationally, then in my
opinion one should adopt the International Standard ISO 8601 for dates
which is to use yyyy-mm-dd and then there can be no ambiguity.

Not sure if [$-409] is of any help. No clue what it is.

No this won't help in this scenario Epinn.
What this does is allows you to use the language of the Regional
Settings for displaying the date
=TEXT(SUBSTITUTE(A1,".","/"),"[$-409]dd.mmmm.yy") or $-809 for UK will
return 01.January.07

=TEXT(SUBSTITUTE(A1,".","/"),"[$-040c]dd.mmmm.yy") will return
01.janvier.07 (France)
=TEXT(SUBSTITUTE(C14,".","/"),"[$-041d]dd.mmm.yy") will return
01.januari.07 (Sweden)

--
Regards

Roger Govier


"Epinn" wrote in message
...
I am typing with one hand. I have to say something important.

If I use the SUBSTITUTE "/" method, then the dd.mm.yyyy format in A1
will be over-ridden by my regional setting which is m/d/yyyy i.e. I
will get Jan instead of Mar.<<

Text to column + custom format (dd.mmm.yyyy) is perfect for me. I say
this method is universal i.e. independent of the regional setting.

Substitute formula + custom format may work for some but not all users
i.e. dependent of regional setting. If it works on the OP's PC, then
what will happen if he/she distributes the worksheet to a user with a
different regional setting? Not sure if [$-409] is of any help. No
clue what it is.

Okay, I said it.

Epinn

"Epinn" wrote in message
...
Roger,

Will you forgive me. I am going backwards and I'll make a point to shut
up. I learned all the fancy formulae to sort text, numbers, number and
text etc. and I have forgotten something as simple as TEXT and DATE.

I am going to post the correct formula for the record. Of course, we
all should use your simple solutions.

A1: 01.03.2007 text format dd.mm.yyyy

B1: =TEXT(DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)),"dd .mmm.yyyy")
returns 01.Mar.2007 (text format).

Must have leading 0 in A1 for this formula to work. e.g. 01 and not
just 1, 03 and not just 3. 1.3.2007 won't work.

This formula is *independent* of my regional setting.

If I use the SUBSTITUTE "/" method, then the dd.mm.yyyy format in A1
will be over-ridden by my regional setting which is m/d/yyyy i.e. I will
get Jan instead of Mar.

Please ignore my very first formula. Don't mean to confuse anyone. My
apology to the OP. Now I have to go and find something to tie up my
fingers to prevent further typing unless critical.

Epinn

"Epinn" wrote in message
...
Sorry, my second formula only works for January. I am fixing it now.

Epinn

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