Formula  Return month as text from a date cell.
Best Answer

If you only need three digit month names the formula is much simpler:
=MID("JanFebMarAprMayJunJulAugSepOctNovDec", (Mth@row * 3)  2, 3)
Answers

Here's what I do:
I have a sheet called "Date Tables". In that sheet I have a column for Month Number and another for Month Name.
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 DecemberI use XSheet references to get the name from the number.
Like this:
=IFERROR(INDEX({Date Tables  Month Name}, MATCH(MONTH(Start@row), {Date Tables  Month Num}, 0)), "")
You only need to set up the XSheet references once per sheet.
Alternatively, you can build a complicated nestedif statement, but I won't.
Craig

Hey,
Thanks very much for this, however, wow, its a rather convoluted workaround for what you'd think should be a simple formula. I'll try it out.

There are posts on the Community with the nestedif example. That's worse.
Before XSheet references, I would have the data on a hidden area of the sheets.
Since I just copy/paste the formulas, it only take a minute or two to setup a new sheet for the functionality.
Craig

Hey Craig  I have created my date tables sheet, and am trying to build out my formula to replicate this but am having a hard time dissecting your formula to adjust to my sheet names, etc.
Can you break it down for me?
=IFERROR(INDEX({Date Tables  Month Name}, MATCH(MONTH(Start@row), {Date Tables  Month Num}, 0)), "")
Thank you!

Yep.
=IFERROR(INDEX({Date Tables  Month Name}, MATCH(MONTH(Start@row), {Date Tables  Month Num}, 0)), "")
MATCH( the month of the date in the [Start] column on this row with the column of data called {Date Tables  Month Num}  which is a single column of the numbers 112.
The last argument (0) is habit and is looking at an unsorted list.
This returns the row number (within the table), so 20180606 would search for 6 and find it on the 7th row (since my table has a header)
INDEX takes that row number (in our example 7) and returns the matching value from the column of data called {Data Tables  Month Name}  another single column with the text of the month's names.
I use INDEX(...,MATCH()) in case someone decides to change the order of the columns, put something between them, or otherwise mess up what I might think of as a beautiful table, perfect in form.
Lastly, I wrap it in IFERROR (returning a blank if one is found) because MONTH() throws an error on blanks.
See image for what the data looks like
Craig

I agree Smartsheet should have a way to format these cells with the Text value of the dates  not very "smart" when compared to Excel.
Its hard to avoid some complexity here but I used a different method only because I am more familiar with these functions. I created master "Lookup Sheet" which can be referenced by any sheet in any workspace and just used the simple table of Month number and Month Name same as Craig did and used Vlookup function.
Smartsheet already allows me to force a date value in the lookup column so I did not need to put an error checking component in other than checking for an empty cell with a simple IF statement but could just as easily use the ERROR function.
=IF([Date]1 = "", "", VLOOKUP(MONTH([Date]1), {Lookup Sheet1}, 2, false))
[Date]1 is the cell I am getting the date from to convert to Month name
{Lookup Sheet1} is the range on the Lookup Sheet

=IF(MONTH(cell with date) = 1, "January" ect function works wonders here. Much easier than listed above.

So I just created a sheet with primary column having each row with the month number and column two with the month written as text e.g January
Then in the primary sheet added the following formula as a VLOOKUP and set as a column formula.
=VLOOKUP(MONTH([date_invoice]@row), {Months Range 1}, 2, false)
Would be nice if there was a formula but the above is a basic workaround without making it overly complicated given the lack of built in function.

Try this
=IF(Month@row = "December",12,IF(Month@row = "November",11,IF(Month@row = "October",10,IF(Month@row = "September",9,IF(Month@row = "August",8,IF(Month@row = "July",7,IF(Month@row = "June",6,IF(Month@row = "May",5,IF(Month@row = "Apirl",4,IF(Month@row = "March",3,IF(Month@row = "February",2,IF(Month@row = "January",1,0))))))))))))

Or
=IF(Month@row = 12,"December",IF(Month@row = 11,"November",IF(Month@row = 10,"October",IF(Month@row = 9,"September",IF(Month@row = 8,"August",IF(Month@row = 7,"July",IF(Month@row = 6,"June",IF(Month@row = 5,"May",IF(Month@row = 4,"April",IF(Month@row = 3,"March",IF(Month@row = 2,"February",IF(Month@row = 1,"January",0))))))))))))

In case you only need to isolate and "numerize" the month from a textual date, you can simply adapt one of the formulas found in this sheet.

The TEXT() function is LOOOONG overdue! Please Smartsheet, an update to some additional handy intrinsic functions should be in the 'Coming Soon' Roadmap  they can't be that difficult to code.

Is this what you mean? If so, not too hard.
=TODAY(1) + ""
yields, for example, “10/10/2018”
The + "" will convert the formula in the parenthesis () to text.

Thanks @Cleversheet, but no.
I'm after the flexibility that Excel has had since day dot. To format text from a cell however I want.
Using the following function syntax:
=TEXT (cell, format)
So, if cell value is date of 10/10/2018, I can format it like "dddd dd ddd, yyyy" to get "Wed 10 Oct, 2018"
Or if cell value is 7135551234, I can format it as a phone number "(###) #######' to get "(713) 5551234"

If you only need three digit month names the formula is much simpler:
=MID("JanFebMarAprMayJunJulAugSepOctNovDec", (Mth@row * 3)  2, 3)
Help Article Resources
Categories
Check out the Formula Handbook template!