Trying to get a date range to appear as a text month

paul112233 · 01/11/22

Hi i have a audit planner and i need a actual date field for another formula in the sheet but would like another column to show the auditees when their audit month has been allocated, therefore i would like a formula to convert the date 31/01/2022 from column A to January 2022 in Column B.

Any ideas?

Heather Duff · 01/11/22

Hi @paul112233 ,

Try this:

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

Christina09 · 01/11/22

Hi @paul112233

I'm not sure if smartsheet have this function, but it can be solved by using the formula below for column B:

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

Nested IF formula with MONTH & YEAR should do it.

Jeff Reisman · 01/11/22

I did this using a lookup chart in a helper sheet.

Make a sheet called "Months" with MonthNum and MonthText columns, and populate with:

MonthNum MonthText

1 January

2 February

etc.

In your audit sheet, we'll concatenate two formulas in Column B, an INDEX/MATCH to find the month name that matches the month number, and a YEAR formula, with a space in between them:

=INDEX({Month Range MonthText}, MATCH(MONTH([Column A]@row), {Month Range MonthNum}, 0)) + " " + YEAR([Column A]@row)

The {Month Range MonthText} and {Month Range MonthNum} references you will create when building your INDEX/MATCH. Click on Reference Another Sheet as you build your INDEX/MATCH and select the column from your "Months" sheet.

paul112233 · 01/11/22

https://community.smartsheet.com/discussion/comment/315759#Comment_315759

Hi Christina, i have success in part, for some reason your formula works up to June and then nothing for July onwards, i have triple checked and redone the formula and cant see any differences but just doesn't pick up those months, just get a blank box, no error message or nothing?

Jeff Reisman · 01/11/22

https://community.smartsheet.com/discussion/comment/315799#Comment_315799

That's a heck of formula Christina wrote!

There's a missing end parentheses in the IF statement for June.

Jeff Reisman · 01/11/22

@paul112233

While the formulas presented by Christina and Heather are impressive, there are two drawbacks to using formulas like these in your use case: the ease of missing a quote or parentheses or something, and the processing overhead in running that many IF statements in a single cell, repeated over and over through a sheet. If you have more than a couple hundred rows in your sheet, you may see significant performance impact in load and save times. Of course, your mileage may vary.

My approach utilizes an INDEX/MATCH with only 12 rows to look through, which takes far less overhead, and the formula is much shorter.

paul112233 · 01/11/22

https://community.smartsheet.com/discussion/comment/315804#Comment_315804

Sorted, excellent thankyou

Christina09 · 01/11/22

@Jeff Reisman HAHAHA... I take that as a compliment.

I'm not a fan of long nested if formula too. Heather's@heather.adams formula is better than mine, not sure why I didn't think of that.

Trying to get a date range to appear as a text month

Answers

Help Article Resources

Categories