help with a formula please

  • Thread starter Thread starter KRK
  • Start date Start date
K

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
 
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
Click to expand...

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
 
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 -
Click to expand...

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
Click to expand...

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
 
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
Click to expand...

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,

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
Click to expand...

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
 
Back
Top