DATE() Month in Letter NOT Number

  • Thread starter Thread starter Neon520
  • Start date Start date
N

Neon520

In Date(year,month,day) function, all need to be number, what if I have month
in letter, how can I use date function?

I've tried a different way by using if, nested function only allow 7 or 8 of
them, so I can't nest the function all the way to December.
If(A1=January,01,if(A1=February,02......and so on)

Anyone have any suggestions on how to do this?

Thanks,
GU
 
I will use three-letter abreviations. Say A1 contains:
jan
in another cell (say Z100):
=MATCH(A1,{"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"},0)

Instead of something like:

=DATE(2008,A1,17)
use
=DATE(2008,Z100,17)
 
If you have the month name like "March" in A1 and the numeric year in B1 and
the day of the month in C1 you could use:

=DATE(B1,MATCH(A1,{"January","February","March","April","May","June","July","August","September","October","November","December"},0),C1)

Tyro
 
Maybe you can use something like:

=DATEVALUE(A1&" 1, 2008")
or
=DATEVALUE(A1 & " " & a2 & ", " & a3)
if a2 contains the date of the month and a3 contains the year.
 
The following will return the date where the month name is in A1, the day of
month is in B1, and the year is in C1.

=TEXT(DATEVALUE(A1&" "&B1&", "&C1),"mmm dd, yyyy")

The value in A1 can be either the 3-character month abbreviation (Jan, Feb,
etc) or the full month name (January, February, etc).


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
hi
I think you need to look at how you have the cell formated.
if your have format set to "March 14,2001" then the formula produces
=date(2007,1,14) = January 14,2008
If you have format set to "03/14/01" then the formula produces
=date(2007,1,14) = 01/14/08

is that what you are trying to do? have a date with all numbers?

works in xp 2003.
regards
FSt1


is this what your are trying to do.
 
Another possibility...

=DATE(A3,MONTH(A1&"-"&A3),A2)

where A1 contains your month in numbers, A2 the day and A3 the year.

Rick
 
Another possibility...
=DATE(A3,MONTH(A1&"-"&A3),A2)

where A1 contains your month in numbers, A2 the day and A3 the year.
Click to expand...

LOL ... where A1 contains your month in LETTERS...

Rick
 
In Date(year,month,day) function, all need to be number, what if I have month
in letter, how can I use date function?

I've tried a different way by using if, nested function only allow 7 or 8 of
them, so I can't nest the function all the way to December.
If(A1=January,01,if(A1=February,02......and so on)

Anyone have any suggestions on how to do this?

Thanks,
GU
Click to expand...

To return the Month number of the spelled out Month in A1, you could use this
formula:

=MATCH(A1,{"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"},0)

or possibly:

=MATCH(LEFT(A1,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0)

Depending on your data, however, you may be able to convert directly to a Date.

For example, if your month is in A1 (spelled out), day of the month in A2 and
year in A3

=--(A2&" "&A1&" "&A3)

or

=--(A1&" "&A2&", "&A3)

will return an Excel date. Just format the result as a date.
--ron
 
=DATEVALUE(A1 & " " & a2 & ", " & a3)

It looks like you can shorten the above to this...

=DATEVALUE(A2&A1&A3)

Rick
 
The following will return the date where the month name is in A1, the day
of month is in B1, and the year is in C1.

=TEXT(DATEVALUE(A1&" "&B1&", "&C1),"mmm dd, yyyy")

The value in A1 can be either the 3-character month abbreviation (Jan,
Feb, etc) or the full month name (January, February, etc).
Click to expand...

I just posted this (using different cell references) to Dave's posting....

=DATEVALUE(B1&A1&C1)

Rick
 
=--(A2&" "&A1&" "&A3)
or

=--(A1&" "&A2&", "&A3)

will return an Excel date. Just format the result as a date.
Click to expand...

Interesting... you can shorten that to this...

=--(A2&A1&A3)

provided A1 contains the month name.

Rick
 
And using Ron's formula as a base, this can be shorted to...

=--(A2&A1&A3)

Rick
 
And using Ron's formula as a base, this can be shorted to...

=--(A2&A1&A3)

Rick
 
Interesting... you can shorten that to this...

=--(A2&A1&A3)

provided A1 contains the month name.

Rick
Click to expand...

That is interesting. I never realized that NO separators could be used,
especially when some separators will give an error

e.g. "12.dec.12"


--ron
 
If this is not a "documented" feature in Excel as a valid way of doing
things, then prepare for MS to suddenly drop it. Using undocumented results
can be very dangerous.

Tyro
 
If this is not a "documented" feature in Excel as a valid way of doing
things, then prepare for MS to suddenly drop it. Using undocumented results
can be very dangerous.

Tyro
Click to expand...

You probably should avoid the DATEDIF worksheet function then, except in
XL2000. If you do suggest its use, you should be very careful to point out its
flaws.
--ron
 
Indeed. Anything that is undocumented, is subject to change, removal etc. MS
has no obligation to support such things.

Tyro
 
I think that the only obligation any software company is to itself (and
stockholders).

Will they do stupid things that cause them to lose market share--probably not.

Will they do things that users don't like, undoubtedly.

I wouldn't hesitate using Ron's formula -- if I could remember it <bg>.
 
Back
Top