Converting Dates to Month and Year

I am trying to use this formula to get my dates to just show the month and year. However, all of my January dates are missing the year. Why is January missing the year?

=IF(MONTH([Start Date]@row = 1, "January", IF(MONTH([Start Date]@row = 2, "February", IF(MONTH([Start Date]@row = 3, "March", IF(MONTH([Start Date]@row = 4, "April", IF(MONTH([Start Date]@row = 5, "May", IF(MONTH([Start Date]@row = 6, "June", IF(MONTH([Start Date]@row = 7, "July", IF(MONTH([Start Date]@row = 8, "August", IF(MONTH([Start Date]@row = 9, "September", IF(MONTH([Start Date]@row = 10, "October", IF(MONTH([Start Date]@row = 11, "November", IF(MONTH([Start Date]@row = 12, "December")))))))))))) + YEAR([Start Date]@row)

Find more posts tagged with

Formulas

Comments

Mike Wilday

I think I discovered the issue. You need to close the MONTH formulas within each IF statement. The missing closing parenthesis was doing something weird to your formula. Try this one:

=IF(MONTH([Start Date]@row) = 1, "January", IF(MONTH([Start Date]@row) = 2, "February", IF(MONTH([Start Date]@row) = 3, "March", IF(MONTH([Start Date]@row) = 4, "April", IF(MONTH([Start Date]@row) = 5, "May", IF(MONTH([Start Date]@row) = 6, "June", IF(MONTH([Start Date]@row) = 7, "July", IF(MONTH([Start Date]@row) = 8, "August", IF(MONTH([Start Date]@row) = 9, "September", IF(MONTH([Start Date]@row) = 10, "October", IF(MONTH([Start Date]@row) = 11, "November", IF(MONTH([Start Date]@row) = 12, "December")))))))))))) + YEAR([Start Date]@row)

Heather K

Oops, yes I already fixed that error but January is still missing the year.

Mike Wilday

Can you share the formula as it exists in that field? Because that seems odd to me. Run a test on a new row to see if it treats it the same?

Heather K

Of course. I added the IFERROR so it doesn't show in the blank fields but here is my current formula. It is working for everything except January which is missing the year.

=IFERROR(IF(MONTH([Start Date]@row) = 1, "January ", IF(MONTH([Start Date]@row) = 2, "February ", IF(MONTH([Start Date]@row) = 3, "March ", IF(MONTH([Start Date]@row) = 4, "April ", IF(MONTH([Start Date]@row) = 5, "May ", IF(MONTH([Start Date]@row) = 6, "June ", IF(MONTH([Start Date]@row) = 7, "July ", IF(MONTH([Start Date]@row) = 8, "August ", IF(MONTH([Start Date]@row) = 9, "September ", IF(MONTH([Start Date]@row) = 10, "October ", IF(MONTH([Start Date]@row) = 11, "November ", IF(MONTH([Start Date]@row) = 12, "December "))))))))))) + YEAR([Start Date]@row)), "")

Heather K

I finally got it. I had a parathensis in the wrong spot.

Mike Wilday

Glad you got it figured out.

Doug Carr

So I just found myself wanting the same thing and I first went to a Month Table in another sheet and used the VLOOKUP formula to get it. Turns out that it was painfully slow given the number of rows in my sheet so I came up with a simpler (and much easier to write) approach.

In my case I wanted the three character abbreviation (Jan, Feb, Mar, etc) so this is what I came up with:

=MID("JanFebMarAprMayJunJulAugSepOctNovDec", ((MONTH(DateColumn@row) - 1) * 3) + 1, 3)

It gets just a bit more unsightly when you want the full month name. In here you have to make all of the month names the same length (padded with spaces to 9 characters . . . the length of the word "September") and then trim the spaces off. Unfortunately Smartsheet does not provide a "TRIM" function but the SUBSTITUTE function seems to work just fine:

=SUBSTITUTE(MID("January February March April May June July August SeptemberOctober November December ", ((MONTH(DateColumn@row) - 1) * 9) + 1, 9), " ", "")

Sign in to join the conversation!

Quick Links

Trending discussions

Writing Formula for Sheet Summary Field
Based on the screenshot below, I am trying to write a formula that will pull in the reason for change on the row where " most recent change" is checked. Thoughts on the best formula? I've tried: =IF([Most Recent Change]:[Most Recent Change] = 1, [Reason for Change]:[Reason for Change] , "") Is there something I can do with…
Help with this Formula
I'm trying to seta formula for the health of the project's tasks based on certain conditions. This is the formula: =IF(OR(AND(Status@row <> "Complete", [End Date]@row < TODAY()), AND(Status@row = "In Progress", VALUE([% Complete]@row ) < 0.3, NETDAYS(TODAY(), [End Date]@row ) <= 30), AND(Status@row = "Not Started", [Start…
Break Up Multi-Entry Row
I'm using a form entry (into an Intake Sheet) in which someone can select multiple locations for the same Initiative. I'm looking to copy the row to a different Master sheet, but would like to have the same row copied multiple times, once per location selected. Intake Sheet: Desired Result in Master Sheet: Basically, I…