 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / January 2008DATE() Month in Letter NOT Number
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)  SignatureGary''s Student - gsnu200765 > In Date(year,month,day) function, all need to be number, what if I have month > in letter, how can I use date function? [quoted text clipped - 7 lines] > 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","April","May","June","July","August","September","October","November","December"},0),C1) Tyro > In Date(year,month,day) function, all need to be number, what if I have > month [quoted text clipped - 9 lines] > 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. > In Date(year,month,day) function, all need to be number, what if I have month > in letter, how can I use date function? [quoted text clipped - 7 lines] > Thanks, > GU SignatureDave Peterson ps. Remember to format the cell with the formula with a nice date format. > Maybe you can use something like: > [quoted text clipped - 18 lines] > > Dave Peterson SignatureDave Peterson > =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 >> =DATEVALUE(A1 & " " & a2 & ", " & a3) > [quoted text clipped - 3 lines] > > 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). SignatureCordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) > In Date(year,month,day) function, all need to be number, what if I have > month [quoted text clipped - 9 lines] > Thanks, > GU > 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. [quoted text clipped - 3 lines] > 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 And using Ron's formula as a base, this can be shorted to... =--(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. [quoted text clipped - 9 lines] > > Rick 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. > In Date(year,month,day) function, all need to be number, what if I have month > in letter, how can I use date function? [quoted text clipped - 7 lines] > Thanks, > GU Another possibility... =DATE(A3,MONTH(A1&"-"&A3),A2) where A1 contains your month in numbers, A2 the day and A3 the year. Rick > In Date(year,month,day) function, all need to be number, what if I have > month [quoted text clipped - 9 lines] > 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 >In Date(year,month,day) function, all need to be number, what if I have month >in letter, how can I use date function? [quoted text clipped - 7 lines] >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 > =--(A2&" "&A1&" "&A3) > [quoted text clipped - 3 lines] > > 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 On Mon, 14 Jan 2008 20:09:48 -0500, "Rick Rothstein \(MVP - VB\)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote: >> =--(A2&" "&A1&" "&A3) >> [quoted text clipped - 11 lines] > >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 > On Mon, 14 Jan 2008 20:09:48 -0500, "Rick Rothstein \(MVP - VB\)" > <rickNOSPAMnews@NOSPAMcomcast.net> wrote: [quoted text clipped - 21 lines] > > --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 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 >>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 [quoted text clipped - 8 lines] > 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>. > Indeed. Anything that is undocumented, is subject to change, removal etc. MS > has no obligation to support such things. [quoted text clipped - 13 lines] > > flaws. > > --ron SignatureDave 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 >Indeed. Anything that is undocumented, is subject to change, removal etc. MS >has no obligation to support such things. [quoted text clipped - 13 lines] >> flaws. >> --ron --ron >>> =--(A2&" "&A1&" "&A3) >>> [quoted text clipped - 13 lines] > > 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\)" <rickNOSPAMnews@NOSPAMcomcast.net> 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... [quoted text clipped - 5 lines] > >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 SignatureBiff Microsoft Excel MVP >>>> =--(A2&" "&A1&" "&A3) >>>> [quoted text clipped - 23 lines] > > Rick |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.