EXTRACT TEXT FROM A DATE

I HAVE A HLOOKUP FUNCTION THAT IS HAVING A HARD TIME DUE TO THE DATE
CRITERIA.

THE IMPORTED FILE HAS THE DATE AS TEXT SUCH AS (JAN - FEB - MAR, ETC).
BECAUSE OF HOW THE SPREADSHEET IS SETUP THE DATE (lookup_value) IS IN THE
FORM OF 1/1/2005 FORMATED TO MMM-YY.

SO I AM TRYING TO CREATE AN INTERMEDIARY FORMULA THAT CAN EXTRACT JAN OUT OF
JAN 04 (1/1/2004) SO THAT HLOOKUP WORKS. (TEXT JAN TO TEXT JAN)

I HAVE TRIED (BUT NOT TO SAY I AM DOING SOMETHING WRONG);
1. MONTH (A1) = 1, FORMATED TO MMM = NOT RECOGNIZED OR #NA
2. ="" & LEFT(A1,3) = 379, FORMATED TO MMM = 379
3. +LEFT(AO11,3) = 379, FORMATED TO MMM = 379
4. ETC.

ANY HELP IN EXTRACTING "JAN" IN TEXT FORM OUT OF 1/1/2004 WOULD BE TRULY
APPRECIATED.

REGARDS

Hi!

TEXT(date,"mmm")

A1 = 1/1/2005 as a true Excel date.

=TEXT(A1,"mmm")

Returns Jan as a text value.

Biff

>-----Original Message-----
>I HAVE A HLOOKUP FUNCTION THAT IS HAVING A HARD TIME DUE
TO THE DATE
>CRITERIA.
>
>THE IMPORTED FILE HAS THE DATE AS TEXT SUCH AS (JAN -
FEB - MAR, ETC).
>BECAUSE OF HOW THE SPREADSHEET IS SETUP THE DATE
(lookup_value) IS IN THE
>FORM OF 1/1/2005 FORMATED TO MMM-YY.
>
>SO I AM TRYING TO CREATE AN INTERMEDIARY FORMULA THAT CAN
EXTRACT JAN OUT OF
>JAN 04 (1/1/2004) SO THAT HLOOKUP WORKS. (TEXT JAN TO
TEXT JAN)
>
>I HAVE TRIED (BUT NOT TO SAY I AM DOING SOMETHING WRONG);
>1. MONTH (A1) = 1, FORMATED TO MMM = NOT RECOGNIZED OR
#NA
>2. ="" & LEFT(A1,3) = 379, FORMATED TO MMM = 379
>3. +LEFT(AO11,3) = 379, FORMATED TO MMM = 379
>4. ETC.
>
>ANY HELP IN EXTRACTING "JAN" IN TEXT FORM OUT OF 1/1/2004
WOULD BE TRULY
>APPRECIATED.
>
>REGARDS
>
>.
>

"Ronbo" <[email protected]> wrote
....
> Any help in extracting "Jan" in text form out of 1/1/2004

Assuming the dates are in col A, A1 down

Try in B1:

=CHOOSE(MONTH(A1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct
","Nov","Dec")

Copy B1 down

(Btw, please release your caps lock. It's very tough to read all caps, and
could be considered "impolite")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Ugh <g>, pl disregard the post ..

Go with Biff's suggestion - much neater
(Why do I keep forgetting that <g> ??)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Thanks to both for your time and help. Max you are correct Biff's is cleaner
and it works, as I am sure yours does also.

Regards

"Ronbo" wrote:

> I HAVE A HLOOKUP FUNCTION THAT IS HAVING A HARD TIME DUE TO THE DATE
> CRITERIA.
>
> THE IMPORTED FILE HAS THE DATE AS TEXT SUCH AS (JAN - FEB - MAR, ETC).
> BECAUSE OF HOW THE SPREADSHEET IS SETUP THE DATE (lookup_value) IS IN THE
> FORM OF 1/1/2005 FORMATED TO MMM-YY.
>
> SO I AM TRYING TO CREATE AN INTERMEDIARY FORMULA THAT CAN EXTRACT JAN OUT OF
> JAN 04 (1/1/2004) SO THAT HLOOKUP WORKS. (TEXT JAN TO TEXT JAN)
>
> I HAVE TRIED (BUT NOT TO SAY I AM DOING SOMETHING WRONG);
> 1. MONTH (A1) = 1, FORMATED TO MMM = NOT RECOGNIZED OR #NA
> 2. ="" & LEFT(A1,3) = 379, FORMATED TO MMM = 379
> 3. +LEFT(AO11,3) = 379, FORMATED TO MMM = 379
> 4. ETC.
>
> ANY HELP IN EXTRACTING "JAN" IN TEXT FORM OUT OF 1/1/2004 WOULD BE TRULY
> APPRECIATED.
>
> REGARDS
>

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

"Ronbo" wrote:

> Thanks to both for your time and help. Max you are correct Biff's is cleaner
> and it works, as I am sure yours does also.
>
> Regards