Month and Year output as text

Options
Josh W
Josh W ✭✭✭✭
edited 05/03/24 in Formulas and Functions

Hi everyone, I need to generate a month and year from a date (text month, not number)

For example, 1/1/24 becomes 'January 2024'. Specifically, I need the day removed.

Is there a more efficient way than this formula:

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

If not, feel free to copy it & use it yourself :-)

Thanks!

Tags:

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Options

    Hi @Josh W,

    I have no idea if this is more efficient, but I can offer another solution.

    In another sheet, you can have the month num and month name and then use an INDEX/MATCH in your main sheet.

    The new sheet would look like this.

    The formula would look like this.

    =INDEX({Month Name}, MATCH(MONTH(Date@row), {Month Number})) + " " + YEAR(Date@row)

    In any event, your solution works so may not be necessary to change.

    Hope this helps,

    Dave

  • Josh W
    Josh W ✭✭✭✭
    Options

    Dave, also a good idea! I'm not going to rebuild it at this point but I like the concept and will probably use it other places. Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I generally use a table as well. The only difference in what I do is that I use the zero in the final portion of the MATCH function to specify an exact match. I have had issues (and seen numerous others having issues) when not specifying for other tables.

    =INDEX({Month Name}, MATCH(MONTH(Date@row), {Month Number}, 0)) + " " + YEAR(Date@row)

    The other thing I have also done (if I have complete control over the table sheet) is skip over the MATCH portion and let the month number itself drive which row pulls from the table.

    =INDEX({Month Name}, MONTH(Date@row)) + " " + YEAR(Date@row)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!