How can I get the month text

Options
Adrian Mandile CHESS
Adrian Mandile CHESS ✭✭✭✭✭
edited 06/02/22 in Formulas and Functions

Based on a date in a cell, how can I easily calculate (in another column) the month text (such as "Jan" or "Feb"). In Excel I would use the TEXT function, with a format of "MMM").

Best Answer

Answers

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Options

    Hi @Adrian @ Chess

    You can create a Month column, make it formula =month(date@row) this will give you the month number.

    Then there are two ways to solve the text portion.

    First: Create a nested if statement for each month, if(month@row=1, "Jan", if(month@row=2, "Feb", etc.

    Second: create a new sheet that has numbers 1 -12 in one column, and months jan, feb, mar, etc in another. Then use an index(text month, match(Month@row, new sheet number column, 0))


    Hope this helps.

    best,

    Brad

    www.MVPOPS.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Adrian @ Chess,

    I hope you're well and safe!

    To add to MVP's excellent advice/answer.

    Here's the entire formula for option 1.

    =
    IF(Month@row <> "", 
    IF(MONTH(Month@row) = 1, "01 January", 
    IF(MONTH(Month@row) = 2, "02 February", 
    IF(MONTH(Month@row) = 3, "03 March", 
    IF(MONTH(Month@row) = 4, "04 April", 
    IF(MONTH(Month@row) = 5, "05 May", 
    IF(MONTH(Month@row) = 6, "06 June", 
    IF(MONTH(Month@row) = 7, "07 July", 
    IF(MONTH(Month@row) = 8, "08 August", 
    IF(MONTH(Month@row) = 9, "09 September", 
    IF(MONTH(Month@row) = 10, " 10 October", 
    IF(MONTH(Month@row) = 11, "11 November", 
    IF(MONTH(Month@row) = 12, "12 December")
    

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Frank Hammond
    Frank Hammond ✭✭✭✭
    Options

    I found this looking for the same assist. However, it did not work as intended and searched some more. I finally have it worked out and thought I would share.

    This helped with polling the month number out for me and works for the entire calendar year with no modifications needed.

    =IF(ISBLANK([Date Column Name]@row), "", MONTH([Date Column Name]@row))

  • John C Murray
    John C Murray ✭✭✭✭
    Answer ✓
    Options

    For three digit month names you can avoid nasty nested IFs by using a much simpler formula:

    =MID("JanFebMarAprMayJunJulAugSepOctNovDec", (Mth@row * 3) - 2, 3)

  • Neil Watson
    Neil Watson ✭✭✭✭✭✭
    Options

    By the same logic:

    =MID("SunMonTueWedThuFriSat", (Day@row) * 3 - 2, 3)

  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭✭✭
    Options

    Thanks! These last two responses were very inventive!

    I was looking to prompt Smartsheet for a CHOOSE() function or a TEXT() custom formatting function.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!