Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ronbo
 
Posts: n/a
Default 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

  #2   Report Post  
Biff
 
Posts: n/a
Default

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

.

  #3   Report Post  
Max
 
Posts: n/a
Default

"Ronbo" 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","J un","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 <atyahoo<dotcom
----


  #4   Report Post  
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----


  #5   Report Post  
Ronbo
 
Posts: n/a
Default

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



  #6   Report Post  
Max
 
Posts: n/a
Default

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
pasting data from a website changes text to date stebro Excel Discussion (Misc queries) 8 August 12th 06 08:39 PM
Extract text someone Excel Worksheet Functions 12 January 23rd 05 01:24 AM
Formula with text and reference to a date cell [email protected] Excel Discussion (Misc queries) 1 January 11th 05 09:15 AM
Advanced Filter using Date represented as text drice Excel Worksheet Functions 1 December 15th 04 05:56 PM
& reference answering as Date Serial # not actual text Kevin M Excel Worksheet Functions 0 November 30th 04 06:57 PM


All times are GMT +1. The time now is 11:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"