How to convert text "JAN" into number 1?

Does anyone have any suggestions on how to convert text "JAN" into number 1?
Does excel have any built in function to do it?
For all the months' symbol into numbers
"JAN" into 1
...
"MAR" into 3
...
"DEC" into 12

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
10
Utf
4/10/2010 2:13:01 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
57232 Views

Similar Articles

[PageSpeed] 44

Expand this formula:

=LOOKUP(A1,{"JAN","FEB"},{1,2})

for the remaining months
-- 
Gary''s Student - gsnu201001


"Eric" wrote:

> Does anyone have any suggestions on how to convert text "JAN" into number 1?
> Does excel have any built in function to do it?
> For all the months' symbol into numbers
> "JAN" into 1
> ..
> "MAR" into 3
> ..
> "DEC" into 12
> 
> Does anyone have any suggestions?
> Thanks in advance for any suggestions
> Eric
-3
Utf
4/10/2010 2:26:02 PM
If your abbreviations are nice, you could use a formula like:

=month("1"&A1)

If your abbreviations aren't nice, it won't work.



Eric wrote:
> 
> Does anyone have any suggestions on how to convert text "JAN" into number 1?
> Does excel have any built in function to do it?
> For all the months' symbol into numbers
> "JAN" into 1
> ..
> "MAR" into 3
> ..
> "DEC" into 12
> 
> Does anyone have any suggestions?
> Thanks in advance for any suggestions
> Eric

-- 

Dave Peterson
2
Dave
4/10/2010 2:38:05 PM
Hello Eric,

In UK you can use
=MONTH("1/"&A1)

In USA it is probably
=MONTH(A1&"/1")

Regards,
Bernd
14
Bernd
4/10/2010 2:38:52 PM
On Sat, 10 Apr 2010 07:26:02 -0700, Gary''s Student
<GarysStudent@discussions.microsoft.com> wrote:

>Expand this formula:
>
>=LOOKUP(A1,{"JAN","FEB"},{1,2})
>
>for the remaining months

The values in the lookup vector have to be in ascending order, so you
need to sort the months like this to have the formula working:

=LOOKUP(A1,{"APR","AUG","DEC","FEB","JAN","JUL","JUN","MAR","MAY","NOV","OCT","SEP"},{4,8,12,2,1,7,6,3,5,11,10,9})

Hope this helps / Lars-�ke
1
Lars
4/10/2010 2:40:59 PM
"Lars-�ke Aspelin" <larske@REMOOVEtelia.com> wrote:
> The values in the lookup vector have to be in ascending
> order, so you need to sort the months like this to have
> the formula working:
> =LOOKUP(A1,{"APR","AUG","DEC","FEB","JAN","JUL","JUN"
> ,"MAR","MAY","NOV","OCT","SEP"},{4,8,12,2,1,7,6,3,5,11,10,9})

Easier and less error-prone:

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


----- original message -----

"Lars-�ke Aspelin" <larske@REMOOVEtelia.com> wrote in message 
news:9d31s5d16qk13gqsddmejfj2n4riprrbla@4ax.com...
> On Sat, 10 Apr 2010 07:26:02 -0700, Gary''s Student
> <GarysStudent@discussions.microsoft.com> wrote:
>
>>Expand this formula:
>>
>>=LOOKUP(A1,{"JAN","FEB"},{1,2})
>>
>>for the remaining months
>
> The values in the lookup vector have to be in ascending order, so you
> need to sort the months like this to have the formula working:
>
> =LOOKUP(A1,{"APR","AUG","DEC","FEB","JAN","JUL","JUN","MAR","MAY","NOV","OCT","SEP"},{4,8,12,2,1,7,6,3,5,11,10,9})
>
> Hope this helps / Lars-�ke 

1
Joe
4/10/2010 3:17:59 PM
Thank you!
-- 
Gary''s Student - gsnu201001


"Lars-Åke Aspelin" wrote:

> On Sat, 10 Apr 2010 07:26:02 -0700, Gary''s Student
> <GarysStudent@discussions.microsoft.com> wrote:
> 
> >Expand this formula:
> >
> >=LOOKUP(A1,{"JAN","FEB"},{1,2})
> >
> >for the remaining months
> 
> The values in the lookup vector have to be in ascending order, so you
> need to sort the months like this to have the formula working:
> 
> =LOOKUP(A1,{"APR","AUG","DEC","FEB","JAN","JUL","JUN","MAR","MAY","NOV","OCT","SEP"},{4,8,12,2,1,7,6,3,5,11,10,9})
> 
> Hope this helps / Lars-Åke
> .
> 
0
Utf
4/10/2010 3:34:01 PM
Wow! magic, I don't know why it works this way, but it works beautifully
Do you have any suggestions on how it works this way?
Thank everyone very much for suggestions
Eric

"Dave Peterson" wrote:

> If your abbreviations are nice, you could use a formula like:
> 
> =month("1"&A1)
> 
> If your abbreviations aren't nice, it won't work.
> 
> 
> 
> Eric wrote:
> > 
> > Does anyone have any suggestions on how to convert text "JAN" into number 1?
> > Does excel have any built in function to do it?
> > For all the months' symbol into numbers
> > "JAN" into 1
> > ..
> > "MAR" into 3
> > ..
> > "DEC" into 12
> > 
> > Does anyone have any suggestions?
> > Thanks in advance for any suggestions
> > Eric
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
4/10/2010 3:35:01 PM
Easy.  The MONTH function gives the month number from a date.  1Jan is 
interpreted as a date.
--
David Biddulph


"Eric" <Eric@discussions.microsoft.com> wrote in message 
news:A19C7129-AD74-406E-825A-50240237D371@microsoft.com...
> Wow! magic, I don't know why it works this way, but it works beautifully
> Do you have any suggestions on how it works this way?
> Thank everyone very much for suggestions
> Eric
>
> "Dave Peterson" wrote:
>
>> If your abbreviations are nice, you could use a formula like:
>>
>> =month("1"&A1)
>>
>> If your abbreviations aren't nice, it won't work.
>>
>>
>>
>> Eric wrote:
>> >
>> > Does anyone have any suggestions on how to convert text "JAN" into 
>> > number 1?
>> > Does excel have any built in function to do it?
>> > For all the months' symbol into numbers
>> > "JAN" into 1
>> > ..
>> > "MAR" into 3
>> > ..
>> > "DEC" into 12
>> >
>> > Does anyone have any suggestions?
>> > Thanks in advance for any suggestions
>> > Eric
>>
>> -- 
>>
>> Dave Peterson
>> .
>> 

0
David
4/10/2010 4:29:25 PM
=MONTH(A1&1)


"Eric" wrote:

> Does anyone have any suggestions on how to convert text "JAN" into number 1?
> Does excel have any built in function to do it?
> For all the months' symbol into numbers
> "JAN" into 1
> ..
> "MAR" into 3
> ..
> "DEC" into 12
> 
> Does anyone have any suggestions?
> Thanks in advance for any suggestions
> Eric
0
Utf
4/10/2010 5:48:01 PM
Just to add to David's response...

I'm amazed at how many different string formats excel can see as a date.  It
comes in particularly handy when doing data|text to columns.



Eric wrote:
> 
> Wow! magic, I don't know why it works this way, but it works beautifully
> Do you have any suggestions on how it works this way?
> Thank everyone very much for suggestions
> Eric
> 
> "Dave Peterson" wrote:
> 
> > If your abbreviations are nice, you could use a formula like:
> >
> > =month("1"&A1)
> >
> > If your abbreviations aren't nice, it won't work.
> >
> >
> >
> > Eric wrote:
> > >
> > > Does anyone have any suggestions on how to convert text "JAN" into number 1?
> > > Does excel have any built in function to do it?
> > > For all the months' symbol into numbers
> > > "JAN" into 1
> > > ..
> > > "MAR" into 3
> > > ..
> > > "DEC" into 12
> > >
> > > Does anyone have any suggestions?
> > > Thanks in advance for any suggestions
> > > Eric
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
4/10/2010 8:32:58 PM
Reply:

Similar Artilces:

how will i convert 05.01.2007 convert 05.Jan.2007 format?
If you're looking for a worksheet function, with 05.01.2007 in cell A1, use =DATE(RIGHT(A1,4),MID(A1,5,2),LEFT(A1,2)) This formula assumes that month and day number have the leading "0" (e.g., "05" not "5"). If you're looking for a VBA solution, use something like Dim S As String Dim Arr As Variant Dim Dt As Date S = "05.01.2007" Arr = Split(S, ".") Dt = DateSerial(Arr(LBound(Arr) + 2), Arr(LBound(Arr) + 1), Arr(LBound(Arr))) Debug.Print Dt -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson ...

creating accounts starting before Jan 1
Is it possible to create a loan (mortgae in my case) account and have the opening date be older than Jan 1 of this year? Please help! Set the clock back on the PC? (Personally, I think this is a really dumb limitation. But I didn't design the thing.) "Jay Behr" <anonymous@discussions.microsoft.com> wrote in message news:245de01c45fbd$264a07a0$a601280a@phx.gbl... > Is it possible to create a loan (mortgae in my case) > account and have the opening date be older than Jan 1 of > this year? > > Please help! ...

For Mike H.'s reply about my msg Jan 20, 'sumif but also filtering
I apologize in advance from everybody if my this mail is aginst general rules of the forum. I' m new in the forum (~1-2 months old), so I do not know much about the rules. He wrote a formula for me My question was "sumif but there may be also filterings" =SUMPRODUCT(--(C1:C10="$");SUBTOTAL(9;OFFSET(B1:B10;ROW(B1:B10)-MIN(ROW(B1:B10));0;1))) His formula is working wonderfully. I am a medium level excel user in general level office environments. for i = 1 to 20 I looked at the formula, worked on it to understand how it works splitted the formula i...

Out-of-Band IE Security Update coming on 21 Jan-10
Microsoft Security Bulletin Advance Notification issued: January 20, 2010 Microsoft Security Bulletins to be issued: January 21, 2010 This is an advance notification of one out-of-band security bulletin that Microsoft is intending to release on January 21, 2010. The bulletin will be for Internet Explorer to address limited attacks against customers of Internet Explorer 6, as well as fixes for vulnerabilities rated Critical that are not currently under active attack. More: http://www.microsoft.com/technet/security/bulletin/ms10-jan.mspx All the info on Security Advisory 979352...

sorting date " MMM-YY" (Jan-05)
Hi, I have a spreadsheets with ticket numbers and the date it was submitted. It is in medium date format (3/18/2005). I have used this formula: =text(A2,"MMM-YY") to conver it to Mar-05. now i have a column with MMMYYY date, however, it will not let me sort it. It is sorting alphabetically, and not in the order of the date. so I have Feb-04,Feb-05, Jan-04,Jan-05 and so on. How can I sort this by month then year? Thank you! It is sorting alphabetically because the data is in TEXT format. In order to sort by date, you'll need to have the data formatted in some kind o...

formatting a date to look like Wed Jan 3
How do I use the custom formatting to get this result? ....Lisa Try custom format as: ddd mmm d -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- <gerry.lisa@gmail.com> wrote in message news:1167703765.802417.306110@k21g2000cwa.googlegroups.com... > How do I use the custom formatting to get this result? > > ...Lisa > Lisa, Use a custom number format of ddd mmm d -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) <gerry.lisa@gmail.com> wrote in message...

Money 2000 2K+1 No Longer Supports Online Banking after 31 Jan
Received a note from our bank today saying Money 2K will no longer support online banking services as of January 31st. Not clear as to the implications of that. I am guessing that Microsoft will no longer provide software updates as necessary but Money 2K will still allow online banking. Or...does this mean that the software can no longer be used for online banking after 31 January. The bank is implying the latter, but that sounds a little harsh and, of course, the notice is rather short. Can anyone here clarify? It may well mean that the bank is dropping OFC (or similar) protocol that ...

What is the formula for series: Jan-04, Feb-04, Mar-04, etc.?
Hi Kitty, =DATE(YEAR(A1),MONTH(A1)+1,1) -- Kind Regards, Niek Otten Microsoft MVP - Excel "kitty" <kitty@discussions.microsoft.com> wrote in message news:2C586891-2883-47B9-A3DE-AF77D4A9799E@microsoft.com... > Hi kitty, One way of doing it is with the formula below: If you have a date is cell A1 (01/01/04) and you want cell B1 to sho you the following month insert in B1 the formula below. =EOMONTH(A1,0)+1 You may have to install the "add in" the ToolPak in order for th formula to work other wise you will get a #NAME?, error. Hope thi helps -- Fa...

Need to find Jan. 2008 calendar/insert in Publisher doc? #2
I have a small business, and I am just learning Pub. 2007. I'm trying to find a monthly calendar that can be inserted into my Publisher invoice, as an object...because I use print invoices. All the templates in Pub. seem to use the current month. dorisitaperu wrote: > I have a small business, and I am just learning Pub. 2007. I'm > trying to find a monthly calendar that can be inserted into my > Publisher invoice, as an object...because I use print invoices. > > All the templates in Pub. seem to use the current month. ================================= Have you tr...

convert 200701 to Jan-07
Hi I have raw data with a date in the format 200701 (as extracted from DB). Does anyone know how I can conver this to Jan-07? Thanks =DATE(LEFT(A1,4),RIGHT(A1,2),1) and format as mmm-yy -- Gary''s Student - gsnu201003 ...

cell, which should display for ex: Jan 1 To Jan 5.
Hi, I have a cell, which should display for ex: Jan 1 To Jan 5. I am confused, as how to incorporate this both the text and the day of the date in the above format. Please help. Regards, Navin Put a date in A1, and then try this: =TEXT(A1,"mmm d")&" To "&TEXT(A1+4,"mmm d") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "navin" <navin.narayana@gm...

Need to find Jan. 2008 calendar/insert in Publisher doc?
I have a small business, and I am just learning Pub. 2007. I'm trying to find a monthly calendar that can be inserted into my Publisher invoice, as an object...because I use print invoices. All the templates in Pub. seem to use the current month. Select the "Apply a Template" from the Format Publication task pane, choose one of the wallet size calendar found at the end of the calendars. The Options are on the right, scroll down to change the date range. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "dorisi...

Latest IE8 update 22 Jan 2010 stops MS Money 2005 operating with b
This IE8 security update has caused MS Money 2005 (UK edition) to stop interacting with UK banks. Symptoms: In Money, connect to banks as normal, Insert bank passwords etc. After bank confirmation screen Money screen stays blank and is not updated from the bank servers. Usually account summaries are shown at this time. Stock market updates are unaffected. Removing the IE 8 update on Windows XP corrects the error ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, clic...

life insurance as of January 1
Hello: As far as the age-based HR Life Insurance setup is concerned, this client would like for any revised premiums to change not as of the employees'/dependents' birthdays but as of January 1 of the calendar year. Is that possible, even? If not, is there a 3rd party HR add-on that cna be used? Thanks! childofthe1980s ...

Total number of Jan 2007 entries in a list of date formatted field
I have a report that captures work done by sales reps. For each entry there exists a date. This report is exported in Excel format and the date is captured as mm/dd/yyyy hh:mm. I would like to have a column that lists the number of work entries by Jan 2007, Feb 2007, Mar 2007, etc. I am at a loss as to how I can conditionally count the occurrence based on the month and the year in this date field. Thanks in advance. =SUMPRODUCT(--(YEAR(B2:B200)=2007),--(MONTH(B2:B200)=1)) where B2:B200 holds the dates you can expand on that if you want to count for an individual =SUMPRODUCT(--(A...

How to convert text "JAN" into number 1?
Does anyone have any suggestions on how to convert text "JAN" into number 1? Does excel have any built in function to do it? For all the months' symbol into numbers "JAN" into 1 ... "MAR" into 3 ... "DEC" into 12 Does anyone have any suggestions? Thanks in advance for any suggestions Eric Expand this formula: =LOOKUP(A1,{"JAN","FEB"},{1,2}) for the remaining months -- Gary''s Student - gsnu201001 "Eric" wrote: > Does anyone have any suggestions on how to convert text "JAN&qu...

Date is displaying 00-Jan-00 when I type in 11/16/09.
When I type in a date field in my excel file the date "11/16/09" it is displaying 00-Jan-00 instead of 16-Nov-09. Why is this happening?? Thanks! Are you sure that you haven't put in =11/16/09 (in other words 11 divided by 16 then divided by 9)? If so, get rid of the = sign. -- David Biddulph "Clark" <Clark@discussions.microsoft.com> wrote in message news:7AA11420-DC26-4B39-8EE3-5168E2ECE729@microsoft.com... > When I type in a date field in my excel file the date "11/16/09" it is > displaying 00-Jan-00 instead of 16-Nov-09. Why i...

Mac Stuff CDs, A to Z, updated 15/Jan/2006, and Win & Mac programs, 'WinMac', 'PC/MaC', 'Win-Mac', 'Multi', 'Multi-Platform', 'MultiFormat', 'MULTIOS', 'HYBRID'
Mac Stuff CDs, A to Z, updated 15/Jan/2006, and Win & Mac programs, 'WinMac', 'PC/MaC', 'Win-Mac', 'Multi', 'Multi-Platform', 'MultiFormat', 'MULTIOS', 'HYBRID' GARAGE SALES, buy, risk-free purchase, working, tested, fully functional, very cheap discounted price, low cost, quality OEM software, -------------------------------------------------------- Abvent Artlantis v4.5 Mac 1CD Abvent.PhotoCAD.v1.0.MacOSX ACD.Systems.Canvas.X.0.2.925.MacOSX Adobe.Acrobat.7.0.Pro.for.Mac.OS 1CD Adobe Acrobat 6.0.0 Professional Mac OSX 1CD ...

Thanks Jon for your Jan 2001 post......it helped me today!
Thanks for this Jon....... I found it doing a Google Search.....used it 21 times today. It works super. Vaya con Dios, Chuck, CABGx3 1. Robin Blackwell Jan 30 2001, 4:23 pm Hi all, I have a large number of Excel 97 Charts that are regularly updated from their corresponding Worksheets. I would like to include on the charts a text title from the Worksheet at cell A1. Is their anyway to link the cell to a text box on each Chart? Any help appreciated. Thanks, Robin 2. Jon Peltier Jan 31 2001, 12:27 am Hi Robin - Easy. Click on the title. Click in the formula bar, press ...

why does =date always return the 1900-Jan-0
Hi, I'm trying to understand why this occurs. I have read info regarding serial dates, how excel stores dates etc but can't find an answer to this. It has cropped up while using criteria in advanced filters - I would have expected to be able to filter on =date. I can get round it by using two date criteria i.e. >=date and <=date but it just seems odd that use of =date returns 00/01/00. If I change to 1904 date system it becomes 01/01/04 Any ideas? Thanks Try using =DATE(year,month,day) or referring to a cell with the date of interest. HTH, Bernie MS Excel MVP "sha...

how do I get excel to format dates before 1 jan 1900
I am transcribing Parish Registers for our local church. Many entries go back to past centuries and we need to be able to search the database/table by date. Hence need date formatting prior to 1900. Any ideas? Ps Using Office 2000 XL and Access All dates previous to 1900 will be text in excel so you need to to search on text Regards, Peo Sjoblom "keithcarteruk" <keithcarteruk@discussions.microsoft.com> wrote in message news:7F81BC98-6FC6-440D-9D3B-426C47F37B4C@microsoft.com... > I am transcribing Parish Registers for our local church. Many entries go back > to pa...

Need to create an excel chart 21 depts jan-feb 04-06 how?
I have a list of data that is basically the wages cost per department per month for 2004 and 2005 and I want to add 2006 to it. I have tried everywhich way of using the chart wizard in excel with no success and have tried a pivotchart/table using the wizard with no success. The regular chart will only display the value on the Y axis and either the department or the month on the X axis, I cannot get it to do for example Sales and show Jan 04, 05 and 06 next to it (the data is laid out this way). Using the Pivot table all I get is a total eg 21 departments..............not even a chart ju...

Month formula always returns Jan
I'm trying to return a month value using the formula below =INDEX($O$20:$O$31,MONTH(C20)) Where I pass months Jan-Dec throught the index formula. The problem is that I'm always getting Jan as a result regardless of the month number. Thanks in advance BW ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Brian, Why not just try =TEXT(C20,"mom") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the em...

Timer after Jan 18, 2038
Does anybody know how does the SetTimer function work internally? I was told that not all timers can work properly when it reaches the date Jan 18, 2038 (overflow). Any idea? Thanks! UINT SetTimer( HWND hWnd, // handle of window for timer messages UINT nIDEvent, // timer identifier UINT uElapse, // time-out value TIMERPROC lpTimerFunc // address of timer procedure ); RB wrote: > Does anybody know how does the SetTimer function work > internally? I was told that not all timers can work > properly when it reaches the date Jan 18, 203...

Custom Calendar Format Example Jan 1-Feb 5 is Jan
I was wanting to know if it is possible to create a custom calendar like the Gregorian or the Hijri. When they use those formats they put a b1 or b2 infront of the format like b1mm/dd/yyyy. I want to be able to key a date in a cell and it gives me the business month not the actual month, unless it falls that way. This is a maunfacturing calendar and isn't standard for business reasons. Just hoping this is possible. Thanks for all the help. -- robertjtucker ------------------------------------------------------------------------ robertjtucker's Profile: http://www.excelforum.com/mem...