Convert Date to a specific month format

Options
Gaurav Chauhan
Gaurav Chauhan ✭✭✭✭✭✭
edited 12/07/23 in Formulas and Functions

Hi Team, Can you please help -

I need to convert a date to a specific format of month. I have the date field and the =MONTH(date@row) field. The output is a month number like 12, or 1. Since the sheet has data expanding into this and next year, I wish to run the date function that omits month as Dec'23 or Jan'24 and so. Coz I have to use that specific format for vlookup.

Thanks.

Answers

  • Lauren Kleitz
    Lauren Kleitz ✭✭✭✭
    Options

    I am not sure if this is the most elegant way to do this but I would create a lookup sheet where one column holds the month numbers (i.e.: 1 , 2, 3, ... 12) and the other holds your desired month abbreviations (i.e.: Jan, Feb, Mar, ... Dec). Then use INDEX/MATCH or VLOOKUP against that table to pull out your month abbreviations.

    To get your final result, you'll wind up with something like: =INDEX({Month Abbreviations}, MATCH(MONTH(date@row), {Month Numbers})) + "'" + RIGHT(Year(date@row), 2)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!