DATE() Month in Letter NOT Number

[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

 

[Gary''s Student]

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)

 

[Tyro]

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

 

[Dave Peterson]

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.

 

[Chip Pearson]

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)

 

[FSt1]

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.

 

[Dave Peterson]

ps. Remember to format the cell with the formula with a nice date format.

 

[Rick Rothstein \(MVP - VB\)]

Another possibility...

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

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

Rick

 

[Rick Rothstein \(MVP - VB\)]

Another possibility...

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

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

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

Rick

 

[Ron Rosenfeld]

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

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

 

[Rick Rothstein \(MVP - VB\)]

=DATEVALUE(A1 & " " & a2 & ", " & a3)

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

=DATEVALUE(A2&A1&A3)

Rick

 

[Rick Rothstein \(MVP - VB\)]

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).

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

=DATEVALUE(B1&A1&C1)

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

or

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

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

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

=--(A2&A1&A3)

provided A1 contains the month name.

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

=--(A2&A1&A3)

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

=--(A2&A1&A3)

Rick

 

[Ron Rosenfeld]

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

=--(A2&A1&A3)

provided A1 contains the month name.

Rick

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

 

[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

 

[Ron Rosenfeld]

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

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

 

[Tyro]

Indeed. Anything that is undocumented, is subject to change, removal etc. MS
has no obligation to support such things.

Tyro

 

[Dave Peterson]

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>.