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

[Chip Pearson]

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]

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.

 

[Epinn]

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

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.

 

[Roger Govier]

Hi

If you are entering text 05.01.07 then you could use the following
Mark the block of cells containing the dates.
Data>Text to Columns>Next>Next>click the Date radio button>ensure the
dropdown tot he right says DMY>Finish
Now Mark the same block of cells>Format>Cells>Number>Custom> 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.

 

[Epinn]

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


Hi

If you are entering text 05.01.07 then you could use the following
Mark the block of cells containing the dates.
Data>Text to Columns>Next>Next>click the Date radio button>ensure the
dropdown tot he right says DMY>Finish
Now Mark the same block of cells>Format>Cells>Number>Custom> 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.

 

[Epinn]

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 format>cells>number.

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

Appreciate your guidance.

Epinn

Hi

If you are entering text 05.01.07 then you could use the following
Mark the block of cells containing the dates.
Data>Text to Columns>Next>Next>click the Date radio button>ensure the
dropdown tot he right says DMY>Finish
Now Mark the same block of cells>Format>Cells>Number>Custom> 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.

 

[Epinn]

Correction:

I meant dd.mmm.yyyy.

Epinn

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


Hi

If you are entering text 05.01.07 then you could use the following
Mark the block of cells containing the dates.
Data>Text to Columns>Next>Next>click the Date radio button>ensure the
dropdown tot he right says DMY>Finish
Now Mark the same block of cells>Format>Cells>Number>Custom> 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.

 

[Roger Govier]

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


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 format>cells>number.

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

Appreciate your guidance.

Epinn

Hi

If you are entering text 05.01.07 then you could use the following
Mark the block of cells containing the dates.
Data>Text to Columns>Next>Next>click the Date radio button>ensure the
dropdown tot he right says DMY>Finish
Now Mark the same block of cells>Format>Cells>Number>Custom> 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.

 

[Epinn]

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

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


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 format>cells>number.

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

Appreciate your guidance.

Epinn

Hi

If you are entering text 05.01.07 then you could use the following
Mark the block of cells containing the dates.
Data>Text to Columns>Next>Next>click the Date radio button>ensure the
dropdown tot he right says DMY>Finish
Now Mark the same block of cells>Format>Cells>Number>Custom> 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.

 

[lady_like]

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?

 

[Epinn]

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 panel>regional and language options>regional options What does short date look like?

You are in good hands with Roger.

Epinn

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?

 

[Epinn]

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

Epinn

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 panel>regional and language options>regional options What does short date look like?

You are in good hands with Roger.

Epinn

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]

Hi

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

 

[Roger Govier]

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 Format>Cells>Number>Custom> dd.mmm.yy

 

[Epinn]

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

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

Epinn

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 panel>regional and language options>regional options What does short date look like?

You are in good hands with Roger.

Epinn

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?

 

[Epinn]

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

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

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

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

Epinn

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 panel>regional and language options>regional options What does short date look like?

You are in good hands with Roger.

Epinn

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]

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


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

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

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

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

Epinn

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 panel>regional and
language options>regional options What does short date look like?

You are in good hands with Roger.

Epinn

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?