How to get Month from abbreviated text to number

[pilot]

Short of a lookup table, how can I get date functions to read 3 char text (JAN, FEB ...) as month number (1, 2...)?

[Aladin Akyurek]

On 2002-02-24 12:41, pilot wrote:
Short of a lookup table, how can I get date functions to read 3 char text (JAN, FEB ...) as month number (1, 2...)?

=MATCH(A1,{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"},0)

will do.

I reckon the formula that you use in your cond format setup is why you're asking for this.

Did you know that, given a date, you can test whether that date is a date e.g., in "Jan" by using:

=TEXT(A1,"mmm")="Jan"

Aladin

PS. I have still a hard time to visualize the lay-out of your worksheet to which you want to apply cond format.

[pilot]

On 2002-02-24 13:01, Aladin Akyurek wrote:

=MATCH(A1,{"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug";"sep";"oct";"nov";"dec"},0)

will do.

I reckon the formula that you use in your cond format setup is why you're asking for this.

Did you know that, given a date, you can test whether that date is a date e.g., in "Jan" by using:

=TEXT(A1,"mmm")="Jan"

Aladin

PS. I have still a hard time to visualize the lay-out of your worksheet to which you want to apply cond format.

I'd be glad to e-mail you a copy. And you are correct, ultimate goal is to black out all Sat, Sun & invalid dates.

Example: (each in separate cell)
1 2 3 .... 31
JAN
FEB
.
.
.
DEC

Intersection of FEB and 31 gives implied date of March 3.

This example didn't format very well here but picture months in Col A and days on Row above Jan beginning with Col B.

[ This Message was edited by: pilot on 2002-02-24 13:21 ]