Hello

I have a text field '2007 Aug' , which I want to turn into a date 15/8/2007
so I can do some calculations with it. I've tried splitting the field etc
but I'm getting into a real mess.

Can someone help me with the formula pls ??

Thanks

KK

On dec. 9, 13:55, "KRK" wrote:
Hello

I have a text field *'2007 Aug' , which I want to turn into a date 15/8/2007
so I can do some calculations with it. I've tried splitting the field etc
but I'm getting into a real mess.

Can someone help me with the formula pls ??

Thanks

KK

Try this formula:
=DATE(LEFT(A1,4),LOOKUP(RIGHT(A1,3),
{"Apr","Aug","Dec","Feb","Jan","Jul","Jun","Mar"," May","Nov","Oct","Sep"},
{4,8,12,2,1,7,6,3,5,11,10,9}),15)
15 is a constant in the formula because you didn't mention the source
of the day.

Regards,
Stefi

KK,

For that string in cell A2, use this formula in a cell formatted as a date:

=DATEVALUE(MID(A2,6,3) & " 15, " & LEFT(A2,4))

HTH,
Bernie
MS Excel MVP


"KRK" wrote in message
...
Hello

I have a text field '2007 Aug' , which I want to turn into a date 15/8/2007 so I can do some
calculations with it. I've tried splitting the field etc but I'm getting into a real mess.

Can someone help me with the formula pls ??

Thanks

KK

On dec. 9, 15:49, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
KK,

For that string in cell A2, use this formula in a cell formatted as a date:

=DATEVALUE(MID(A2,6,3) & " 15, " & LEFT(A2,4))

HTH,
Bernie
MS Excel MVP

"KRK" wrote in message

...



Hello

I have a text field *'2007 Aug' , which I want to turn into a date 15/8/2007 so I can do some
calculations with it. I've tried splitting the field etc but I'm getting into a real mess.

Can someone help me with the formula pls ??

Thanks

KK- Idézett szöveg elrejtése -

- Idézett szöveg megjelenítése -

It's nice and short but depends on regional date settings. My solution
works with any setting.
Regards,
Stefi

It's nice and short but depends on regional date settings. My solution
works with any setting.
Regards,
Stefi

Stefi,

Since the month is a 3 letter string, and the day is the 15th (and there is no 15th month), it
should work with any regional date settings. My Excel handles

7 Aug 2007
the same as
Aug 7, 2007

HTH,
Bernie
MS Excel MVP

Thanks for the help, I got it going OK

KK


"KRK" wrote in message
...
Hello

I have a text field '2007 Aug' , which I want to turn into a date
15/8/2007 so I can do some calculations with it. I've tried splitting the
field etc but I'm getting into a real mess.

Can someone help me with the formula pls ??

Thanks

KK

On dec. 10, 14:56, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
It's nice and short but depends on regional date settings. My solution
works with any setting.
Regards,
Stefi

Stefi,

Since the month is a 3 letter string, and the day is the 15th *(and there is no 15th month), it
should work with any regional date settings. My Excel handles

7 Aug 2007
the same as
Aug 7, 2007

HTH,
Bernie
MS Excel MVP

Hi Bernie,

I was surprised when your formula really worked with my Hungarian
regional settings. The date format in this setting is yyyy.mm.dd. I
made some additional tests and found that
1. your formula doesn't depend on regional settings if 3 letter month
names are the same in English and in the national language, e.g. Jan,
Feb, Aug, etc. but when they are different, e.g. Mar-Már, Apr-Ápr, May-
Máj, etc. the formula doesn't work.
2. =DATEVALUE("15/Aug/2009") works even with Hungarian regional
settings, but =DATEVALUE("2009/Aug/15") doesn't, =DATEVALUE
("2009/8/15") works, =DATEVALUE("15.Aug.2009") also works but
=DATEVALUE("Aug.15.2009") doesn't. I didn't find much logic in it.

My formula was also wrong because I forgot that some 3 letter month
names are different in English and in national languages.

Regards,
Stefi

Stefi,

I'm surprised too - I was thinking more along the lines of the 8/15/2007 vs 15/8/2007 settiongs.
I just assumed English was the language used.

HTH,
Bernie
MS Excel MVP


"Stefi" wrote in message
...
On dec. 10, 14:56, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
It's nice and short but depends on regional date settings. My solution
works with any setting.
Regards,
Stefi

Stefi,

Since the month is a 3 letter string, and the day is the 15th (and there is no 15th month), it
should work with any regional date settings. My Excel handles

7 Aug 2007
the same as
Aug 7, 2007

HTH,
Bernie
MS Excel MVP

Hi Bernie,

I was surprised when your formula really worked with my Hungarian
regional settings. The date format in this setting is yyyy.mm.dd. I
made some additional tests and found that
1. your formula doesn't depend on regional settings if 3 letter month
names are the same in English and in the national language, e.g. Jan,
Feb, Aug, etc. but when they are different, e.g. Mar-Már, Apr-Ápr, May-
Máj, etc. the formula doesn't work.
2. =DATEVALUE("15/Aug/2009") works even with Hungarian regional
settings, but =DATEVALUE("2009/Aug/15") doesn't, =DATEVALUE
("2009/8/15") works, =DATEVALUE("15.Aug.2009") also works but
=DATEVALUE("Aug.15.2009") doesn't. I didn't find much logic in it.

My formula was also wrong because I forgot that some 3 letter month
names are different in English and in national languages.

Regards,
Stefi