| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Month Number to Month Text Abbreviation
Example:
Cell A1 = 11/17/2007 Using a function/formula, I would like cell B1 to display "Nov" (without using any date formats) . . . as if I had simply typed "Nov" (without the double quotes) in cell B1. I would greatly appreciate any help. Thanks, Bob |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Month Number to Month Text Abbreviation
=TEXT(A1,"mmm")
HTH, Paul "Bob" wrote in message ... Example: Cell A1 = 11/17/2007 Using a function/formula, I would like cell B1 to display "Nov" (without using any date formats) . . . as if I had simply typed "Nov" (without the double quotes) in cell B1. I would greatly appreciate any help. Thanks, Bob |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Month Number to Month Text Abbreviation
Try this:
=TEXT(A1,"mmm") HTH, Elkar "Bob" wrote: Example: Cell A1 = 11/17/2007 Using a function/formula, I would like cell B1 to display "Nov" (without using any date formats) . . . as if I had simply typed "Nov" (without the double quotes) in cell B1. I would greatly appreciate any help. Thanks, Bob |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Month Number to Month Text Abbreviation
=CHOOSE(MONTH(A1),"Jan","Feb","Mar","Apr","May","J un","Jul","Aug","Sep","Oct","Nov","Dec")
Using date formats is a lot easier. -- Gary''s Student - gsnu200715 "Bob" wrote: Example: Cell A1 = 11/17/2007 Using a function/formula, I would like cell B1 to display "Nov" (without using any date formats) . . . as if I had simply typed "Nov" (without the double quotes) in cell B1. I would greatly appreciate any help. Thanks, Bob |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Month Number to Month Text Abbreviation
=A1
Custom Format cells as mmm "Bob" wrote: Example: Cell A1 = 11/17/2007 Using a function/formula, I would like cell B1 to display "Nov" (without using any date formats) . . . as if I had simply typed "Nov" (without the double quotes) in cell B1. I would greatly appreciate any help. Thanks, Bob |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Month Number to Month Text Abbreviation
Thanks to everyone for their help!
Bob "Bob" wrote: Example: Cell A1 = 11/17/2007 Using a function/formula, I would like cell B1 to display "Nov" (without using any date formats) . . . as if I had simply typed "Nov" (without the double quotes) in cell B1. I would greatly appreciate any help. Thanks, Bob |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Month Number to Month Text Abbreviation
Hi there,
I used the function but the result show "Jan" only, please help!!! A1 : 15/4/07 B1 : =IF(ISBLANK(A1),"",MONTH(A1)) 'show 4 in the cell C1 : =Text(IF(ISBLANK(A1),"",MONTH(A1)),"mmm") 'show Jan in the cell "Elkar" wrote: Try this: =TEXT(A1,"mmm") HTH, Elkar "Bob" wrote: Example: Cell A1 = 11/17/2007 Using a function/formula, I would like cell B1 to display "Nov" (without using any date formats) . . . as if I had simply typed "Nov" (without the double quotes) in cell B1. I would greatly appreciate any help. Thanks, Bob |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Month Number to Month Text Abbreviation
Try it like this:
=IF(ISNUMBER(A1),TEXT(A1,"mmm"),"") Biff "Andy" wrote in message ... Hi there, I used the function but the result show "Jan" only, please help!!! A1 : 15/4/07 B1 : =IF(ISBLANK(A1),"",MONTH(A1)) 'show 4 in the cell C1 : =Text(IF(ISBLANK(A1),"",MONTH(A1)),"mmm") 'show Jan in the cell "Elkar" wrote: Try this: =TEXT(A1,"mmm") HTH, Elkar "Bob" wrote: Example: Cell A1 = 11/17/2007 Using a function/formula, I would like cell B1 to display "Nov" (without using any date formats) . . . as if I had simply typed "Nov" (without the double quotes) in cell B1. I would greatly appreciate any help. Thanks, Bob |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Month Number to Month Text Abbreviation
Hi Biff,
Same result to show Jan only, why???? "T. Valko" wrote: Try it like this: =IF(ISNUMBER(A1),TEXT(A1,"mmm"),"") Biff "Andy" wrote in message ... Hi there, I used the function but the result show "Jan" only, please help!!! A1 : 15/4/07 B1 : =IF(ISBLANK(A1),"",MONTH(A1)) 'show 4 in the cell C1 : =Text(IF(ISBLANK(A1),"",MONTH(A1)),"mmm") 'show Jan in the cell "Elkar" wrote: Try this: =TEXT(A1,"mmm") HTH, Elkar "Bob" wrote: Example: Cell A1 = 11/17/2007 Using a function/formula, I would like cell B1 to display "Nov" (without using any date formats) . . . as if I had simply typed "Nov" (without the double quotes) in cell B1. I would greatly appreciate any help. Thanks, Bob |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Month Number to Month Text Abbreviation
Same result to show Jan only, why????
That doesn't make any sense! I use the U.S. date format mm/dd/yyyy A1 = 4/15/2007 =IF(ISNUMBER(A1),TEXT(A1,"mmm"),"") Correctly returns: Apr If A1 is either *empty* or contains TEXT the formula will return a blank "". If it's returning Jan then there's something "funny" about the date. If the cell contains a numeric 0 but you suppressed 0 display this will return Jan. Format cell A1 as GENERAL. When you do that the value should be 39187. If it isn't then your date of 15/4/07 is not a true Excel date but then the formula should return a blank. So, there's something you're not telling me! Biff "Andy" wrote in message ... Hi Biff, Same result to show Jan only, why???? "T. Valko" wrote: Try it like this: =IF(ISNUMBER(A1),TEXT(A1,"mmm"),"") Biff "Andy" wrote in message ... Hi there, I used the function but the result show "Jan" only, please help!!! A1 : 15/4/07 B1 : =IF(ISBLANK(A1),"",MONTH(A1)) 'show 4 in the cell C1 : =Text(IF(ISBLANK(A1),"",MONTH(A1)),"mmm") 'show Jan in the cell "Elkar" wrote: Try this: =TEXT(A1,"mmm") HTH, Elkar "Bob" wrote: Example: Cell A1 = 11/17/2007 Using a function/formula, I would like cell B1 to display "Nov" (without using any date formats) . . . as if I had simply typed "Nov" (without the double quotes) in cell B1. I would greatly appreciate any help. Thanks, Bob |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting Month Number to Month Text Abbreviation
Try formatting A1 as an unambiguous date.
Give it a custom format of: mmmm dd, yyyy What do you see? Andy wrote: Hi Biff, Same result to show Jan only, why???? "T. Valko" wrote: Try it like this: =IF(ISNUMBER(A1),TEXT(A1,"mmm"),"") Biff "Andy" wrote in message ... Hi there, I used the function but the result show "Jan" only, please help!!! A1 : 15/4/07 B1 : =IF(ISBLANK(A1),"",MONTH(A1)) 'show 4 in the cell C1 : =Text(IF(ISBLANK(A1),"",MONTH(A1)),"mmm") 'show Jan in the cell "Elkar" wrote: Try this: =TEXT(A1,"mmm") HTH, Elkar "Bob" wrote: Example: Cell A1 = 11/17/2007 Using a function/formula, I would like cell B1 to display "Nov" (without using any date formats) . . . as if I had simply typed "Nov" (without the double quotes) in cell B1. I would greatly appreciate any help. Thanks, Bob -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| converting month name to number | Excel Worksheet Functions | |||
| Function or formula to convert "text" month to number of month? | Excel Discussion (Misc queries) | |||
| converting Month name to a number | Excel Discussion (Misc queries) | |||
| Function or formula to convert "text" month to number of month? | Excel Discussion (Misc queries) | |||
| Function or formula to convert "text" month to number of month | Excel Discussion (Misc queries) | |||
Linear Mode
