help with a formula please

[KRK]

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

 

[Stefi]

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

 

[Bernie Deitrick]

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

 

[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








- 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

 

[Bernie Deitrick]

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

 

[KRK]

Thanks for the help, I got it going OK

KK

 

[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

 

[Bernie Deitrick]

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,

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