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

Options
✭✭✭

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?

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
edited 01/11/22
Options

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.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

That's a heck of formula Christina wrote!

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

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
edited 01/11/22
Options

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.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭
Options

Sorted, excellent thankyou

• ✭✭✭✭✭✭
Options

@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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!