Converting Dates to Month and Year

Heather K

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)

 

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.

=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), "")

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), " ", "")