Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Steve Vincent
 
Posts: n/a
Default Function or formula to convert "text" month to number of month?

Is there a function to convert the text name of a month (e.g., "April") to
its corresponding number (e.g., "4")? If not, the only workaround I can
think of would be a long, nested IF function (e.g., =IF(A1="January",
"1",IF(A1="February", "2", . . . etc. . . ))))))))

TIA for any help offered,
Steve
  #2   Report Post  
Posted to microsoft.public.excel.misc
Kevin Vaughn
 
Posts: n/a
Default Function or formula to convert "text" month to number of month

You're welcome.
--
Kevin Vaughn


"Steve Vincent" wrote:

Perfect! Thanks Kevin!
Steve

"Kevin Vaughn" wrote:

With April in B18, this worked for me:
=MONTH(DATEVALUE(B18 & " 1, 2006"))
--
Kevin Vaughn


"Steve Vincent" wrote:

Is there a function to convert the text name of a month (e.g., "April") to
its corresponding number (e.g., "4")? If not, the only workaround I can
think of would be a long, nested IF function (e.g., =IF(A1="January",
"1",IF(A1="February", "2", . . . etc. . . ))))))))

TIA for any help offered,
Steve

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Function or formula to convert "text" month to number of month?

Does anoyone know how to do the reverse. Convert month number to text.

Ex: 1 = Jan, 2= Feb

"Steve Vincent" wrote:

Is there a function to convert the text name of a month (e.g., "April") to
its corresponding number (e.g., "4")? If not, the only workaround I can
think of would be a long, nested IF function (e.g., =IF(A1="January",
"1",IF(A1="February", "2", . . . etc. . . ))))))))

TIA for any help offered,
Steve

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default Function or formula to convert "text" month to number of month?

There is no built in conversion either way but it is easy to make one

=VLOOKUP(A1,{1,"Jan";2,"Feb";3,"Mar";4,"Apr";5,"Ma y";6,"Jun";7,"Jul";8,"Aug";9,"Sep";10,"Oct";11,"No v";12,"Dec"},2,0)


with your number in A1

You might want to wrap it in an IF function in case a1 is blank

=IF(A1="","",VLOOKUP(A1,{1,"Jan";2,"Feb";3,"Mar";4 ,"Apr";5,"May";6,"Jun";7,"Jul";8,"Aug";9,"Sep";10, "Oct";11,"Nov";12,"Dec"},2,0))



--
Regards,

Peo Sjoblom



"Jennnifer" wrote in message
...
Does anoyone know how to do the reverse. Convert month number to text.

Ex: 1 = Jan, 2= Feb

"Steve Vincent" wrote:

Is there a function to convert the text name of a month (e.g., "April")
to
its corresponding number (e.g., "4")? If not, the only workaround I can
think of would be a long, nested IF function (e.g., =IF(A1="January",
"1",IF(A1="February", "2", . . . etc. . . ))))))))

TIA for any help offered,
Steve



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Function or formula to convert "text" month to number of month?

=text(date(2007,a1,1),"mmm")

if A1 contained the number (1-12).

Jennnifer wrote:

Does anoyone know how to do the reverse. Convert month number to text.

Ex: 1 = Jan, 2= Feb

"Steve Vincent" wrote:

Is there a function to convert the text name of a month (e.g., "April") to
its corresponding number (e.g., "4")? If not, the only workaround I can
think of would be a long, nested IF function (e.g., =IF(A1="January",
"1",IF(A1="February", "2", . . . etc. . . ))))))))

TIA for any help offered,
Steve


--

Dave Peterson


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Function or formula to convert "text" month to number of month

Thanks Dave!

"Dave Peterson" wrote:

=text(date(2007,a1,1),"mmm")

if A1 contained the number (1-12).

Jennnifer wrote:

Does anoyone know how to do the reverse. Convert month number to text.

Ex: 1 = Jan, 2= Feb

"Steve Vincent" wrote:

Is there a function to convert the text name of a month (e.g., "April") to
its corresponding number (e.g., "4")? If not, the only workaround I can
think of would be a long, nested IF function (e.g., =IF(A1="January",
"1",IF(A1="February", "2", . . . etc. . . ))))))))

TIA for any help offered,
Steve


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
referencing named formula using INDIRECT function [email protected] Excel Worksheet Functions 19 May 11th 05 09:48 AM
CONVERT Function Disappered in Excel Gord Dibben Excel Discussion (Misc queries) 3 April 13th 05 07:59 PM
Formula to convert to month Mel Excel Worksheet Functions 2 December 2nd 04 12:52 AM


All times are GMT +1. The time now is 12:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"