Excel Text Formula
Hi,
I am trying to use an Excel formula to extract the full month from the date. I am using the excel formula: text([Due Date]@row,"mmmm") and I am getting an error. Is there a similar smartsheet formula ?
Thank you !
Best Answer
-
I have it in a separate sheet, because I refer to it a LOT. So the formula I gave you is a "cross sheet" formula (it refers to another sheet).
If you wanted to leave it in your same sheet, you'll format the formula a little bit differently.
=INDEX([Month Name]:[Month Name], MATCH(MONTH([Due Date]@row), [Month Number]:[Month Number], 0))
This should get rid of your error.
Answers
-
There isn't a function that returns the text month, but here's what I do:
I have a sheet that contains a bunch of pieces of Date related info:
So I'll have a formula that looks up the numeric month from your date field:
=MONTH(([Due Date]@row)
Then use that in an Index/Match lookup to come up with the month name:
=INDEX({Month Name}, MATCH(MONTH([Due Date]@row), {Month Number}, 0))
-
Thank you so much for your help. Does this data have to be in a separate or the same sheet? I am obviously not doing something right bc I am getting an error. Please see the screenshot attached.
I used =INDEX({Month Name}, MATCH(MONTH([Due Date]@row), {Month Number}, 0))
Thank you!
-
I have it in a separate sheet, because I refer to it a LOT. So the formula I gave you is a "cross sheet" formula (it refers to another sheet).
If you wanted to leave it in your same sheet, you'll format the formula a little bit differently.
=INDEX([Month Name]:[Month Name], MATCH(MONTH([Due Date]@row), [Month Number]:[Month Number], 0))
This should get rid of your error.
-
@MCorbin Thank you so much! You've been so helpful!
-
Thank you @MCorbin! This worked for my sheet. :)
-
I know this has be solved already, but there is a simple way to do it with a formula using a static string where each month is padded with spaces.
= SUBSTITUTE( MID( "January February March April May June July August September October November December ", [Month Number]@row * 10 - 9, 10 ), " ", "" )
The month values (including the padding) are all 10 characters long. The MID function determines where the start of the month value is based on the [Month Number], and then extracts all 10 characters. The SUBSTITUTE function then removes the trailing spaces
