Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert month number to string
I have a master sheet in which date is stored in columns by month. Row 1 has
the year and row 2 has the month number (1 to 12). There are presentation sheets that reference subsets of the master data and on those sheets I would like to display a 3 char string for the month e.g. 'JAN' for month 1. Any suggestions, please? -- Graham |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert month number to string
On Wed, 15 Nov 2006 18:11:02 -0800, Graham
wrote: I have a master sheet in which date is stored in columns by month. Row 1 has the year and row 2 has the month number (1 to 12). There are presentation sheets that reference subsets of the master data and on those sheets I would like to display a 3 char string for the month e.g. 'JAN' for month 1. Any suggestions, please? =TEXT(DATE(2006,A2,1),"mmm") (The year and day of the month are irrelevant for this purpose). --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert month number to string
Try this:
=TEXT(A1&"-1","mmm") "Graham" wrote: I have a master sheet in which date is stored in columns by month. Row 1 has the year and row 2 has the month number (1 to 12). There are presentation sheets that reference subsets of the master data and on those sheets I would like to display a 3 char string for the month e.g. 'JAN' for month 1. Any suggestions, please? -- Graham |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert month number to string
Thanks Ron
So simple when you have the knowledge, or can figue out what to search for. Thanks again. -- Graham "Ron Rosenfeld" wrote: On Wed, 15 Nov 2006 18:11:02 -0800, Graham wrote: I have a master sheet in which date is stored in columns by month. Row 1 has the year and row 2 has the month number (1 to 12). There are presentation sheets that reference subsets of the master data and on those sheets I would like to display a 3 char string for the month e.g. 'JAN' for month 1. Any suggestions, please? =TEXT(DATE(2006,A2,1),"mmm") (The year and day of the month are irrelevant for this purpose). --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert month number to string
On Wed, 15 Nov 2006 18:52:02 -0800, Graham
wrote: Thanks Ron So simple when you have the knowledge, or can figue out what to search for. Thanks again. You're welcome. Glad to help. --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert month number to string
=TEXT(A1&"-1","mmm")
Neat solution!! but I think you meant to reference cell A2 not A1. Also, dependant upon your regional settings, (in the UK for example) you would need to use =TEXT("1-"&A2,"mmm") -- Regards Roger Govier "Teethless mama" wrote in message ... Try this: =TEXT(A1&"-1","mmm") "Graham" wrote: I have a master sheet in which date is stored in columns by month. Row 1 has the year and row 2 has the month number (1 to 12). There are presentation sheets that reference subsets of the master data and on those sheets I would like to display a 3 char string for the month e.g. 'JAN' for month 1. Any suggestions, please? -- Graham |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert month number to string
On Thu, 16 Nov 2006 12:18:13 -0000, "Roger Govier"
wrote: =TEXT(A1&"-1","mmm") Neat solution!! but I think you meant to reference cell A2 not A1. Also, dependant upon your regional settings, (in the UK for example) you would need to use =TEXT("1-"&A2,"mmm") That's an advantage of using the DATE worksheet function -- it is not locale dependent. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List File Properties - Author | Excel Worksheet Functions | |||
Convert displayed number to absolute value | Excel Discussion (Misc queries) | |||
How to COnvert text string to number | Excel Discussion (Misc queries) | |||
How to convert string to a date | Excel Worksheet Functions | |||
Convert week number into calendar month? | Excel Worksheet Functions |