G
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
C
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)
=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)
G
Guest
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.
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.
E
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.
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.
R
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.
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.
E
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.
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.
E
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.
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.
E
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.
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.
R
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.
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.
E
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.
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.
G
Guest
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?
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?
E
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?
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?
E
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
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?
R
Roger Govier
Hi
Then try
=TEXT(SUBSTITUTE(A1,".","/"),"dd.mmm.yy")
Then try
=TEXT(SUBSTITUTE(A1,".","/"),"dd.mmm.yy")
R
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
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
E
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?
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?
E
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?
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?
R
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.
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?
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?
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.