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)
Comments
-
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)
-
Oops, yes I already fixed that error but January is still missing the year.
-
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?
-
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)), "")
-
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), "")
-
Glad you got it figured out.
-
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), " ", "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!