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


        
0
lady_like (1)
2/8/2007 3:28:01 AM
excel.newusers 15348 articles. 1 followers. Follow

17 Replies
608 Views

Similar Articles

[PageSpeed] 51

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" <lady_like@discussions.microsoft.com> wrote in message 
news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
> 


0
chip1 (1821)
2/8/2007 3:59:16 AM
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" <lady_like@discussions.microsoft.com> wrote in message 
> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
> > 
> 
> 
> 
0
ladylike (2)
2/8/2007 9:16:00 AM
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 =3D month i.e. Jan.

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

This formula returns 05.Jan.2007

Hope this helps.

Epinn

"lady_like" <ladylike@discussions.microsoft.com> wrote in message =
news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
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.=20


"Chip Pearson" wrote:

> If you're looking for a worksheet function, with 05.01.2007 in cell =
A1, use
>=20
> =3DDATE(RIGHT(A1,4),MID(A1,5,2),LEFT(A1,2))
>=20
> This formula assumes that month and day number have the leading "0" =
(e.g.,=20
> "05" not "5").
>=20
> If you're looking for a VBA solution, use something like
>=20
>     Dim S As String
>     Dim Arr As Variant
>     Dim Dt As Date
>     S =3D "05.01.2007"
>     Arr =3D Split(S, ".")
>     Dt =3D DateSerial(Arr(LBound(Arr) + 2), Arr(LBound(Arr) + 1),=20
> Arr(LBound(Arr)))
>     Debug.Print Dt
>=20
>=20
>=20
> --=20
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email address is on the web site)
>=20
>=20
>=20
>=20
>=20
> "lady_like" <lady_like@discussions.microsoft.com> wrote in message=20
> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
> >=20
>=20
>=20
>

0
someone6932 (228)
2/8/2007 10:02:33 AM
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.
-- 
Regards

Roger Govier


"lady_like" <ladylike@discussions.microsoft.com> wrote in message 
news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> 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" <lady_like@discussions.microsoft.com> wrote in message
>> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
>> >
>>
>>
>> 


0
roger5293 (1125)
2/8/2007 10:41:51 AM
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" <roger@technologyNOSPAM4u.co.uk> wrote in message =
news:uAw7D32SHHA.5068@TK2MSFTNGP03.phx.gbl...
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=20
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=20
whilst you do it, and then play that back when needed.
--=20
Regards

Roger Govier


"lady_like" <ladylike@discussions.microsoft.com> wrote in message=20
news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> hi Chip,
>
> i want to convert the 01 from "05.01.2007" to a text. 01 is month of=20
> 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=20
>> A1, use
>>
>> =3DDATE(RIGHT(A1,4),MID(A1,5,2),LEFT(A1,2))
>>
>> This formula assumes that month and day number have the leading "0"=20
>> (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 =3D "05.01.2007"
>>     Arr =3D Split(S, ".")
>>     Dt =3D DateSerial(Arr(LBound(Arr) + 2), Arr(LBound(Arr) + 1),
>> Arr(LBound(Arr)))
>>     Debug.Print Dt
>>
>>
>>
>> --=20
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>> (email address is on the web site)
>>
>>
>>
>>
>>
>> "lady_like" <lady_like@discussions.microsoft.com> wrote in message
>> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
>> >
>>
>>
>>=20



0
someone6932 (228)
2/8/2007 10:57:11 AM
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. =20

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

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" <roger@technologyNOSPAM4u.co.uk> wrote in message =
news:uAw7D32SHHA.5068@TK2MSFTNGP03.phx.gbl...
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=20
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=20
whilst you do it, and then play that back when needed.
--=20
Regards

Roger Govier


"lady_like" <ladylike@discussions.microsoft.com> wrote in message=20
news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> hi Chip,
>
> i want to convert the 01 from "05.01.2007" to a text. 01 is month of=20
> 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=20
>> A1, use
>>
>> =3DDATE(RIGHT(A1,4),MID(A1,5,2),LEFT(A1,2))
>>
>> This formula assumes that month and day number have the leading "0"=20
>> (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 =3D "05.01.2007"
>>     Arr =3D Split(S, ".")
>>     Dt =3D DateSerial(Arr(LBound(Arr) + 2), Arr(LBound(Arr) + 1),
>> Arr(LBound(Arr)))
>>     Debug.Print Dt
>>
>>
>>
>> --=20
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>> (email address is on the web site)
>>
>>
>>
>>
>>
>> "lady_like" <lady_like@discussions.microsoft.com> wrote in message
>> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
>> >
>>
>>
>>=20



0
someone6932 (228)
2/8/2007 11:35:30 AM
Correction:

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

I meant dd.mmm.yyyy.

Epinn

"Epinn" <someone@example.com.NO_SPAM> wrote in message =
news:uqX8vC3SHHA.412@TK2MSFTNGP02.phx.gbl...
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" <roger@technologyNOSPAM4u.co.uk> wrote in message =
news:uAw7D32SHHA.5068@TK2MSFTNGP03.phx.gbl...
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=20
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=20
whilst you do it, and then play that back when needed.
--=20
Regards

Roger Govier


"lady_like" <ladylike@discussions.microsoft.com> wrote in message=20
news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> hi Chip,
>
> i want to convert the 01 from "05.01.2007" to a text. 01 is month of=20
> 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=20
>> A1, use
>>
>> =3DDATE(RIGHT(A1,4),MID(A1,5,2),LEFT(A1,2))
>>
>> This formula assumes that month and day number have the leading "0"=20
>> (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 =3D "05.01.2007"
>>     Arr =3D Split(S, ".")
>>     Dt =3D DateSerial(Arr(LBound(Arr) + 2), Arr(LBound(Arr) + 1),
>> Arr(LBound(Arr)))
>>     Debug.Print Dt
>>
>>
>>
>> --=20
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>> (email address is on the web site)
>>
>>
>>
>>
>>
>> "lady_like" <lady_like@discussions.microsoft.com> wrote in message
>> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
>> >
>>
>>
>>=20




0
someone6932 (228)
2/8/2007 11:38:16 AM
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" <someone@example.com.NO_SPAM> wrote in message 
news:%2303r%23W3SHHA.1600@TK2MSFTNGP05.phx.gbl...
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

"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:uAw7D32SHHA.5068@TK2MSFTNGP03.phx.gbl...
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.
-- 
Regards

Roger Govier


"lady_like" <ladylike@discussions.microsoft.com> wrote in message
news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> 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" <lady_like@discussions.microsoft.com> wrote in message
>> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
>> >
>>
>>
>>




0
roger5293 (1125)
2/8/2007 11:59:50 AM
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" <roger@technologyNOSPAM4u.co.uk> wrote in message =
news:eHt#oi3SHHA.4260@TK2MSFTNGP06.phx.gbl...
Hi Epinn

Yes, if the date separator is a period, then the entry is a valid Excel=20
date which can be formatted in any of the various ways of formatting the =

date, without needing any additional transformation of the "raw" data.

--=20
Regards

Roger Govier


"Epinn" <someone@example.com.NO_SPAM> wrote in message=20
news:%2303r%23W3SHHA.1600@TK2MSFTNGP05.phx.gbl...
Roger,

I really like the fact that you are always detailed and I definitely=20
have benefited a lot.

Just want to say how I interpret 05.01.2007.  I see it as a short date=20
under regional setting for a country in Europe.  Out of curiosity, I=20
went to control panel and experimented.  First one I chose was German=20
(Germany) and I got 08.02.2007 for today.  Now I know the German format=20
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=20
true, can we skip the text to column step and go straight to custom=20
format?  Maybe we don't even have to use custom format, but can just=20
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

"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message=20
news:uAw7D32SHHA.5068@TK2MSFTNGP03.phx.gbl...
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.
--=20
Regards

Roger Govier


"lady_like" <ladylike@discussions.microsoft.com> wrote in message
news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> 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
>>
>> =3DDATE(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 =3D "05.01.2007"
>>     Arr =3D Split(S, ".")
>>     Dt =3D DateSerial(Arr(LBound(Arr) + 2), Arr(LBound(Arr) + 1),
>> Arr(LBound(Arr)))
>>     Debug.Print Dt
>>
>>
>>
>> --=20
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>> (email address is on the web site)
>>
>>
>>
>>
>>
>> "lady_like" <lady_like@discussions.microsoft.com> wrote in message
>> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
>> >
>>
>>
>>





0
someone6932 (228)
2/8/2007 6:44:20 PM
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.
> 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.
> -- 
> Regards
> 
> Roger Govier
> 
> 
> "lady_like" <ladylike@discussions.microsoft.com> wrote in message 
> news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> > 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" <lady_like@discussions.microsoft.com> wrote in message
> >> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
ladylike (2)
2/9/2007 1:47:00 AM
A1=3D05.01.2007=20

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:

=3DLEFT(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? =20

You are in good hands with Roger. =20

Epinn=20

"lady_like" <ladylike@discussions.microsoft.com> wrote in message =
news:F946E2C5-7940-487A-B472-FEEF5F437C24@microsoft.com...
hi Roger/Epinn,

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

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


"Roger Govier" wrote:

> Hi
>=20
> 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=20
> dropdown tot he right says DMY>Finish
> Now Mark the same block of cells>Format>Cells>Number>Custom> dd.mmm.yy
>=20
> If you are going to be doing it regularly, you could record a macro=20
> whilst you do it, and then play that back when needed.
> --=20
> Regards
>=20
> Roger Govier
>=20
>=20
> "lady_like" <ladylike@discussions.microsoft.com> wrote in message=20
> news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> > 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
> >>
> >> =3DDATE(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 =3D "05.01.2007"
> >>     Arr =3D Split(S, ".")
> >>     Dt =3D DateSerial(Arr(LBound(Arr) + 2), Arr(LBound(Arr) + 1),
> >> Arr(LBound(Arr)))
> >>     Debug.Print Dt
> >>
> >>
> >>
> >> --=20
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >> (email address is on the web site)
> >>
> >>
> >>
> >>
> >>
> >> "lady_like" <lady_like@discussions.microsoft.com> wrote in message
> >> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
> >> >
> >>
> >>
> >>=20
>=20
>=20
>

0
someone6932 (228)
2/9/2007 7:25:16 AM
Sorry, my second formula only works for January.  I am fixing it now.

Epinn =20

"Epinn" <someone@example.com.NO_SPAM> wrote in message =
news:uVdhztBTHHA.4784@TK2MSFTNGP03.phx.gbl...
A1=3D05.01.2007=20

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:

=3DLEFT(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? =20

You are in good hands with Roger. =20

Epinn=20

"lady_like" <ladylike@discussions.microsoft.com> wrote in message =
news:F946E2C5-7940-487A-B472-FEEF5F437C24@microsoft.com...
hi Roger/Epinn,

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

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


"Roger Govier" wrote:

> Hi
>=20
> 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=20
> dropdown tot he right says DMY>Finish
> Now Mark the same block of cells>Format>Cells>Number>Custom> dd.mmm.yy
>=20
> If you are going to be doing it regularly, you could record a macro=20
> whilst you do it, and then play that back when needed.
> --=20
> Regards
>=20
> Roger Govier
>=20
>=20
> "lady_like" <ladylike@discussions.microsoft.com> wrote in message=20
> news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> > 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
> >>
> >> =3DDATE(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 =3D "05.01.2007"
> >>     Arr =3D Split(S, ".")
> >>     Dt =3D DateSerial(Arr(LBound(Arr) + 2), Arr(LBound(Arr) + 1),
> >> Arr(LBound(Arr)))
> >>     Debug.Print Dt
> >>
> >>
> >>
> >> --=20
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >> (email address is on the web site)
> >>
> >>
> >>
> >>
> >>
> >> "lady_like" <lady_like@discussions.microsoft.com> wrote in message
> >> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
> >> >
> >>
> >>
> >>=20
>=20
>=20
>


0
someone6932 (228)
2/9/2007 7:33:28 AM
Hi

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

-- 
Regards

Roger Govier


"lady_like" <ladylike@discussions.microsoft.com> wrote in message 
news:F946E2C5-7940-487A-B472-FEEF5F437C24@microsoft.com...
> 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.
>> 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.
>> -- 
>> Regards
>>
>> Roger Govier
>>
>>
>> "lady_like" <ladylike@discussions.microsoft.com> wrote in message
>> news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
>> > 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" <lady_like@discussions.microsoft.com> wrote in message
>> >> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
>> >> >
>> >>
>> >>
>> >>
>>
>>
>> 


0
roger5293 (1125)
2/9/2007 7:45:10 AM
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

-- 
Regards

Roger Govier


"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:eU8y$4BTHHA.4260@TK2MSFTNGP06.phx.gbl...
> Hi
>
> Then try
> =TEXT(SUBSTITUTE(A1,".","/"),"dd.mmm.yy")
>
> -- 
> Regards
>
> Roger Govier
>
>
> "lady_like" <ladylike@discussions.microsoft.com> wrote in message 
> news:F946E2C5-7940-487A-B472-FEEF5F437C24@microsoft.com...
>> 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.
>>> 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.
>>> -- 
>>> Regards
>>>
>>> Roger Govier
>>>
>>>
>>> "lady_like" <ladylike@discussions.microsoft.com> wrote in message
>>> news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
>>> > 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" <lady_like@discussions.microsoft.com> wrote in 
>>> >> message
>>> >> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
>>> >> >
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
> 


0
roger5293 (1125)
2/9/2007 8:07:29 AM
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:  =3DTEXT(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" <someone@example.com.NO_SPAM> wrote in message =
news:OdDncyBTHHA.496@TK2MSFTNGP06.phx.gbl...
Sorry, my second formula only works for January.  I am fixing it now.

Epinn =20

"Epinn" <someone@example.com.NO_SPAM> wrote in message =
news:uVdhztBTHHA.4784@TK2MSFTNGP03.phx.gbl...
A1=3D05.01.2007=20

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:

=3DLEFT(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? =20

You are in good hands with Roger. =20

Epinn=20

"lady_like" <ladylike@discussions.microsoft.com> wrote in message =
news:F946E2C5-7940-487A-B472-FEEF5F437C24@microsoft.com...
hi Roger/Epinn,

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

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


"Roger Govier" wrote:

> Hi
>=20
> 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=20
> dropdown tot he right says DMY>Finish
> Now Mark the same block of cells>Format>Cells>Number>Custom> dd.mmm.yy
>=20
> If you are going to be doing it regularly, you could record a macro=20
> whilst you do it, and then play that back when needed.
> --=20
> Regards
>=20
> Roger Govier
>=20
>=20
> "lady_like" <ladylike@discussions.microsoft.com> wrote in message=20
> news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> > 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
> >>
> >> =3DDATE(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 =3D "05.01.2007"
> >>     Arr =3D Split(S, ".")
> >>     Dt =3D DateSerial(Arr(LBound(Arr) + 2), Arr(LBound(Arr) + 1),
> >> Arr(LBound(Arr)))
> >>     Debug.Print Dt
> >>
> >>
> >>
> >> --=20
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >> (email address is on the web site)
> >>
> >>
> >>
> >>
> >>
> >> "lady_like" <lady_like@discussions.microsoft.com> wrote in message
> >> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
> >> >
> >>
> >>
> >>=20
>=20
>=20
>



0
someone6932 (228)
2/9/2007 9:13:03 AM
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. =20

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" <someone@example.com.NO_SPAM> wrote in message =
news:u736WtCTHHA.4028@TK2MSFTNGP02.phx.gbl...
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:  =3DTEXT(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" <someone@example.com.NO_SPAM> wrote in message =
news:OdDncyBTHHA.496@TK2MSFTNGP06.phx.gbl...
Sorry, my second formula only works for January.  I am fixing it now.

Epinn =20

"Epinn" <someone@example.com.NO_SPAM> wrote in message =
news:uVdhztBTHHA.4784@TK2MSFTNGP03.phx.gbl...
A1=3D05.01.2007=20

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:

=3DLEFT(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? =20

You are in good hands with Roger. =20

Epinn=20

"lady_like" <ladylike@discussions.microsoft.com> wrote in message =
news:F946E2C5-7940-487A-B472-FEEF5F437C24@microsoft.com...
hi Roger/Epinn,

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

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


"Roger Govier" wrote:

> Hi
>=20
> 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=20
> dropdown tot he right says DMY>Finish
> Now Mark the same block of cells>Format>Cells>Number>Custom> dd.mmm.yy
>=20
> If you are going to be doing it regularly, you could record a macro=20
> whilst you do it, and then play that back when needed.
> --=20
> Regards
>=20
> Roger Govier
>=20
>=20
> "lady_like" <ladylike@discussions.microsoft.com> wrote in message=20
> news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> > 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
> >>
> >> =3DDATE(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 =3D "05.01.2007"
> >>     Arr =3D Split(S, ".")
> >>     Dt =3D DateSerial(Arr(LBound(Arr) + 2), Arr(LBound(Arr) + 1),
> >> Arr(LBound(Arr)))
> >>     Debug.Print Dt
> >>
> >>
> >>
> >> --=20
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >> (email address is on the web site)
> >>
> >>
> >>
> >>
> >>
> >> "lady_like" <lady_like@discussions.microsoft.com> wrote in message
> >> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
> >> >
> >>
> >>
> >>=20
>=20
>=20
>




0
someone6932 (228)
2/9/2007 10:59:23 AM
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" <someone@example.com.NO_SPAM> wrote in message 
news:etLqclDTHHA.5016@TK2MSFTNGP05.phx.gbl...
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" <someone@example.com.NO_SPAM> wrote in message 
news:u736WtCTHHA.4028@TK2MSFTNGP02.phx.gbl...
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" <someone@example.com.NO_SPAM> wrote in message 
news:OdDncyBTHHA.496@TK2MSFTNGP06.phx.gbl...
Sorry, my second formula only works for January.  I am fixing it now.

Epinn

"Epinn" <someone@example.com.NO_SPAM> wrote in message 
news:uVdhztBTHHA.4784@TK2MSFTNGP03.phx.gbl...
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

"lady_like" <ladylike@discussions.microsoft.com> wrote in message 
news:F946E2C5-7940-487A-B472-FEEF5F437C24@microsoft.com...
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.
> 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.
> -- 
> Regards
>
> Roger Govier
>
>
> "lady_like" <ladylike@discussions.microsoft.com> wrote in message
> news:8FA083B8-A789-4E25-A1AD-8F23C0A7D19E@microsoft.com...
> > 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" <lady_like@discussions.microsoft.com> wrote in message
> >> news:2A8C80B6-BDC1-4EDF-AE35-02FA463DAF31@microsoft.com...
> >> >
> >>
> >>
> >>
>
>
>





0
roger5293 (1125)
2/9/2007 12:43:35 PM
Reply:

Similar Artilces:

Converting Excel to Space delimited
Hi, I have a spreadsheet that I am trying to convert to a space delimite file, so that I can load it into a DB2 database. The only way I can find to do this is to save the file as a spac delimited *.prn file. However, for some reason Excel inserts a lin break after every 241 characters. I need to find a way of exportin long lines (up to around 1000 characters, spread across severa columns) to a space delimited file without Excel inserting a line brea until the end of the row. Has anyone found a way around this? Thanks for any help -- mv586 ------------------------------------------...

mail will not open
when trying to open some...not all...emails I get the message "there was an error opening this message.....an error has occurred". This doesn't happen to all messages nor does it happen to all messages from the same sender. "velona" <velona@discussions.microsoft.com> wrote in message news:5CFA4037-506D-4362-B110-13A9915D825A@microsoft.com... > when trying to open some...not all...emails I get the message "there was > an > error opening this message.....an error has occurred". > This doesn't happen to all messages nor doe...

Outlook 2007 & Winrar
Hi, I just installed Winrar 3.62 in Vista RTM with Outlook 2007 in it also. Now whenever I right click something and get the dialog "compress and email" or "compress to untitled.rar and e-mail" nothing happens. I looked it up at Winrar and faq over there said that the e-mail has to support MAPI???? I'm not sure what any of this means, but it does nothing at all, not even open Outlook to send e-mails. My friend said it works fine for her, Vista RTM, Outlook 2007, and Winrar 3.62. Hmm, nothing happens when I try to compress and e-mail anything. Any help? Jeff ...

Excel 2007 will not refresh cells
I am currently using Excel 2007. I have a spreadsheet that I want to use to do some analysis of various situations. So here is my problem. I have several columns that have totals at the bottom. I have no problem getting the columns to total initially. However, if I plug in another number in the column, the total stays the same and won't refresh or re-total the column. I don't recall that this was a problem when I worked with Excel 2003. Can someone please tell me how to get the totals to update when I enter some new numbers in the column? Thanks in advance for the...

Date format #15
I did something in a spreadsheet where now anywhere I enter a date I get a 5 digit number instead of the date. If I click on the number, I can see the date in the edit window. When I try to format the cell it doesn't change. Can anyone tell me how to undue it? Try tools|options|view tab and uncheck formulas. You may have hit the shortcut key toggle (ctrl-`) control-backquote. It's to the left of the 1/! on my USA keyboard. Steve wrote: > > I did something in a spreadsheet where now anywhere I > enter a date I get a 5 digit number instead of the date. > If I click ...

Convert activity (email, letter, etc) to case bug?
When converting any activity to a case in CRM 4.0, using the default Convert To Case button, I noticed the case's picklist caseorigincode not being set properly. This picklist holds values matching the activity types, such as email and letter. Is there a way to set this using relationships, workflow or whatever? I did notice that my picklist values are all above 200.000, and I cannot set them to something like 1,2,3. Perhaps these old values are registrered for such a mapping. Unfortunately, they were deleted some time ago and reset again. Anyone? -- CRM consultant ...

Convert Month, Week, Year to date
What is the easiest way to convert three values - Month, Week, Year to a date? Date would be the Monday of the week specified. Pete You'll need to specify how you define the week number within a month? Is week 1 always 1st to 7th? Presumably the Monday of week 5 can actually fall in the next month? Perhaps: =DATE(C2,A2,B2*7-6)+MOD(7-WEEKDAY(DATE(C2,A2,B2*7-6),3),7) -- David Biddulph Pete Hay wrote: > What is the easiest way to convert three values - Month, Week, Year > to a date? Date would be the Monday of the week specified. > Pete I'm trying to conv...

subform will not refresh
I have a subform for jobs to be at list - when I am int the actual form it requeries and shows only the ones checked off - when I go to the mainform with the jobs to be at list as a subform - it does not refresh or requery unless I close and open back up again. I had added a refresh command button with the following code: DoCmd.RunCommand acCmdRefresh when i press the refresh button it refreshes the job list to be at I add this code to the Onload of the Main form - don't want user to have to press refresh button but it doesn't refresh jobs to be at list unless press ...

convert value in word. For Exampe Rs.115.00 convert into word as .
I want to show the a sum of few numbers in word. For Example- 90.00 25.00 ------- 115.00 -------- (Rupees : One hundred and fifteen only.) Hi Shakti, Here is a function I gave some time ago thatb caters for Rupees, Lakhs, Crore and Paise. To call it, enter something like =SpellNumber(115) in a cell Function SpellNumber(ByVal MyNumber, Optional incRupees As Boolean = True) Dim Crores, Lakhs, Rupees, Paise, Temp Dim DecimalPlace As Long, Count As Long Dim myLakhs, myCrores ReDim Place(9) As String Place(2) = " Thousand &quo...

You will pass all the exams at 1st attempt No more headaches and worry about spending the time and money to attempt another exam again. You will pass all of your exams, every time www.FreeExamKing.com
You will pass all the exams at 1st attempt No more headaches and worry about spending the time and money to attempt another exam again. You will pass all of your exams, every time www.FreeExamKing.com ...

Format of date field in CRM
I have just created an email template in CRM. I have put a date field that is specified as "date only" in the schema but when it is mail merged the date and time appears in the email. Is there a way to format these fields on merge ie only the date? I am aware that I could push the fields contents into a redundant text field "on save" and then hide the field "on load" but thought there may be a way to format these fields. ...

Outlook 2007 SP2 on Windows 7
Hi All, I have upgraded my laptop to Windows 7. After I installed office 2007 sp2, in outlook 2007 when I create new email or reply email, the mouse buttons seems not work any more. I don't have highlight and right click. It was working before I installed sp2. I try reinstall everything without any luck. Is this a outlook bug? I don't think it's a bug - did you try Help, Office Diagnostics? Have you rebooted since installing SP2? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ ...

Re: Access 2007 05-20-07
U�ytkownik ">" <Jan Kowalski <Tutaj wpisujesz �w�j nick> napisa� w wiadomo�ci news:... > > > -- > msnews.microsoft.com > Uzytkownik "Michael" <mmatuszak@comcast.net> napisal w wiadomosci > news:1172517310.689465.209590@z35g2000cwz.googlegroups.com... >> The reason I ask is that I, too, work with a highly programmed Access >> database (some 15,000 lines of VBA code, much of it automating data >> entry on forms -- and believe me, it's very tight code). In Access 97, >> 2000, 2002, and 2003, no performance pr...

knowledge base 09-05-06
Hi there I am using Microsoft CRM 3.0 and want to activate the knowledge base. As adminitrator I can see the knowledge base but when I log in as normal user I do not see it. Can anyone tell me how to do this? I tried to change the security role and changed the service and article tabs. But this didn't helped me. Any solutions? TIA Christian ...

How do I get Outlook 2007 to print only the weeks I select?
I can't seem to get the Print Range to work in Outlook 2007. No matter what range I put in, Outlook prints a 6 week calendar (except for Feb, which it prints 5 weeks). I've tried deselecting the "print exactly one month," but that button seems to be useless for me. I have SP2 installed. I tried using the Calendar Printing Assistant, and while it will print the month out correctly, it will not print my category colors, so it is not useful for me. Bottom line is I need to see more appointments on my monthly view print out, and I can do this if I print 4 or...

messages will not display on Outlook Express
I cannot get my messages to display the text of the message. I can send and receive, but when I actually try to open a message the message will pop up, but without text. The hourglass icon is displaying, seeming like it's trying to pull the message up but it never comes. Does anyone know what I should do? anonymous@discussions.microsoft.com <anonymous@discussions.microsoft.com> wrote: > I cannot get my messages to display the text of the > message. I can send and receive, but when I actually try > to open a message the message will pop up, but without > text. ...

Outlook 2007 #59
Is there a way to use Outlook 2007 on one user account and Outlook 2003 on another User account on the same computer wph2 schrieb: > Is there a way to use Outlook 2007 on one user account and Outlook 2003 on > another User account on the same computer It's not possible to have more than one version of Outlook installed on a PC. btw. why would you prefer OL2003 for some accounts? Franz "Franz Leu" wrote: > wph2 schrieb: > > Is there a way to use Outlook 2007 on one user account and Outlook 2003 on > > another User account on the same computer > ...

hey 05-31-07
what am i doing??? ...

with Publisher or Office.Will I be able to access old files docume
I am frustrated with my Open Office org. program for many reasons. I would like to use Publisher or Office or? Will I be able to access, email, documents etc files previously made on another program if I download Publisher or Office or ? Erica wrote: > I am frustrated with my Open Office org. program for many reasons. > I would like to use Publisher or Office or? > Will I be able to access, email, documents etc files previously made on > another program if I download Publisher or Office or ? Office applications cannot open OpenDocument files. You will have to convert them to O...

My Money Side will not stop
I read the FAQ for Money 2002 and it stated A) Click Help in MoneySide, then Settings. Under "Open MoneySide" select "Never" and then "Done." I select "Never" but there is no "Done" I can find and My Money leaves for this time only to return again. Is there any way to kill money from running in the background. It is not running in MSCONFIG on my XP sp2 system that I can find. Thanks Don I wish I had a way to test this here to tell you how to do it and validate the FAQ. Hopefully somebody who can still test on M02 will join the thread....

Changing Time format in Date column
Hi, When I review email messages in Windows Live Mail I would like to see the time displayed in 24 hour format by each message. Ie, instead of Date: 05/03/2010 4:35 pm I would like it be shown as 05/03/2010 16:35. Any help from anyone would be greatfully appreciated. Thanks, Jon. "Jon" <Jon@discussions.microsoft.com> wrote in message news:54D681D7-2367-434B-AC7A-29AD80A4A828@microsoft.com... > When I review email messages in Windows Live Mail I would like to see the time displayed in 24 hour format by each message. Ie, instead of Date: 05/03/2010 4:35 pm...

Converting word tables to excel
I have a document that describes a new piece of software. The cost for each feature is described in a word table. I would like to extract these tables (with a makro?) into an excel sheet automatically. Any ideas anybody? Eckard ...

Office 2008 shuts down when I try to convert docx. to doc.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I put together a rather large document (40 pages with four pictures) as a docx. Now I'm trying to save it as a doc., but Word shuts down everytime I try. Is there anything I can do? thanks, chris Hi Chris - First confirm that you're up-to-date on OS X (10.5.4), Office (12.1.2), and that you've repaired disk permissions. Those are the primary causes of problems like this. If that doesn't resolve the issue we'll need more information about the file. It isn't so much the size - actually, 40 pages...

Microsoft Office Professional 2007 trial
I am downloading this version of Office to trial it while taking a class. I already have the 2003 version and I'm not sure I will purchase the full 2007 package once my trial is over. If I uninstall the program once my trial is over, will my pre-existing Office (2003) be available the same way it was before? Why not install the Office 2007 Trial into a different directory than the Office 2003 that is installed at present. This way one will not bother the other and you will have no issues once the 2007 Trial is uninstalled -- Peter Please Reply to Newsgroup for the ben...

linked contacts returnes an error
I'm using Outlook 2007 under Vista Home Premium. (I should probably also mention I imported contacts a year ago from Outlook 2000.) Just now I opened a Contact and clicked on a linked Contact. (This 'linked' contact is shown under the Notes field and next to the small Contact window at the bottom of the Contact.) I go to click on that linked Contact and receive the following message - Cannot perform the requested operation. The command selected is not valid for this recipient. An internal function returned an error.[end] Why do I have this problem with Outlook 2007 whe...