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","ju l","aug","sep","oct","nov","dec"},0)

Instead of something like:

=DATE(2008,A1,17)
use
=DATE(2008,Z100,17)

--
Gary''s Student - gsnu200765


"Neon520" wrote:

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

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","A pril","May","June","July","August","September","Oc tober","November","December"},0),C1)

Tyro

"Neon520" wrote in message
...
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

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.

Neon520 wrote:

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

--

Dave Peterson

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)


"Neon520" wrote in message
...
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

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.

"Neon520" wrote:

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

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

Dave Peterson wrote:

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.

Neon520 wrote:

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

--

Dave Peterson

--

Dave Peterson

Another possibility...

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

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

Rick


"Neon520" wrote in message
...
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

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

On Mon, 14 Jan 2008 15:25:02 -0800, Neon520
wrote:

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";"M ay";"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).

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.

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
=DATEVALUE(A1 & " " & a2 & ", " & a3)

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

=DATEVALUE(A2&A1&A3)

Rick

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

=--(A2&A1&A3)

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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

On Mon, 14 Jan 2008 20:09:48 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

=--(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

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

"Ron Rosenfeld" wrote in message
...
On Mon, 14 Jan 2008 20:09:48 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

=--(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

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

On Mon, 14 Jan 2008 18:22:59 -0800, "Tyro" wrote:

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

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

Tyro

"Ron Rosenfeld" wrote in message
...
On Mon, 14 Jan 2008 18:22:59 -0800, "Tyro" wrote:

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

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.

Tyro wrote:

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

Tyro

"Ron Rosenfeld" wrote in message
...
On Mon, 14 Jan 2008 18:22:59 -0800, "Tyro" wrote:

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

--

Dave Peterson

Each to his own, then.

I noted you did not caution about the DATEDIF inconsistencies in a previous
thread, nor mention concern about it's use when it is not documented; so I
guess there are only certain undocumented features that concern you.

I'd have a lot more concern about using MOD with certain classes of numbers
(also undocumented), or using DATEDIF, than I would about date_text
conversions.
--ron



On Mon, 14 Jan 2008 18:53:16 -0800, "Tyro" wrote:

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

Tyro

"Ron Rosenfeld" wrote in message
.. .
On Mon, 14 Jan 2008 18:22:59 -0800, "Tyro" wrote:

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


--ron

=--(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

That is interesting. I never realized that NO separators could be used,

I'm not sure why, but when I saw Dave's DATEVALUE formula, it reminded me of
the date format that was used on my orders from my days in the Air Force...
I always thought 15Jan08 was a nice format for some reason (although it
"looked" better when the year wasn't decipherable as a day value; e.g.,
15Jan67), so it occurred to me to try it in the DATEVALUE function... and it
worked. Then I saw your --(<datestring) format and figured I would try it
there too... and, again, it worked.

Rick

On Tue, 15 Jan 2008 12:53:55 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

I'm not sure why, but when I saw Dave's DATEVALUE formula, it reminded me of
the date format that was used on my orders from my days in the Air Force...
I always thought 15Jan08 was a nice format for some reason (although it
"looked" better when the year wasn't decipherable as a day value; e.g.,
15Jan67), so it occurred to me to try it in the DATEVALUE function... and it
worked. Then I saw your --(<datestring) format and figured I would try it
there too... and, again, it worked.

Rick

I remember that format, now that you mention it. And I, too, was dealing with
years that could not be mistaken for day (or month) values <g.
--ron

my days in the Air Force...

Me too!

SAC - Kincheloe MI, Castle CA

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
=--(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

That is interesting. I never realized that NO separators could be used,

I'm not sure why, but when I saw Dave's DATEVALUE formula, it reminded me
of the date format that was used on my orders from my days in the Air
Force... I always thought 15Jan08 was a nice format for some reason
(although it "looked" better when the year wasn't decipherable as a day
value; e.g., 15Jan67), so it occurred to me to try it in the DATEVALUE
function... and it worked. Then I saw your --(<datestring) format and
figured I would try it there too... and, again, it worked.

Rick