Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

FORMULA TO PRESENT THE MONTH (NAME) IN A CELL

✭✭✭✭✭
edited 09/11/23 in Formulas and Functions

I created a formula using -MONTH to extract the Month Number, but i want that number to convert to the name (JAN, FEB, etc)

I started with pulling the Month Number from the Intake Date column;

=MONTH([Intake Date - \[Select Date\]]@row)

then, I used an =IF to convert the "1" to equal JAN:

=IF(Month1 = "1", "JAN")


I thought i could use an OR statement (multiple times) to name other months beside "1" (ie. "2"=FEB, 3=MAR...etc)

I tested it with a simple additional month number "2" , but it doesnt work.

=IF(OR(Month1 = "1", "JAN", (Month1 = "2", "FEB"))

Looking for guidance here so i can name each of the 12 months for the relative number value provided.


thank you!

Best Answer

  • ✭✭✭✭✭
    Answer ✓

    Hey Andree'

    Thank you for your input, while i was waiting on a reply, i found this to work for me:

    =IF(Month1 = "1", "JAN", IF(Month1 = "2", "FEB", IF(Month1 = "3", "MAR", IF(Month1 = "4", "APR", IF(Month1 = "5", "MAY", IF(Month1 = "6", "JUN", IF(Month1 = "7", "JUL", IF(Month1 = "8", "AUG", IF(Month1 = "9", "SEP", IF(Month1 = "10", "OCT", IF(Month1 = "11", "NOV", IF(Month1 = "12", "DEC"))))))))))))


    I did check you string, i got INVALID DATA TYPE, but i think it would work, i may have not lined up the month@row correctly.


    I'm good to go tho and thanks again!

    Rick

Answers

  • Community Champion

    Hi @Rick Girard

    I hope you're well and safe!

    Try something like this.

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

  • ✭✭✭✭✭
    Answer ✓

    Hey Andree'

    Thank you for your input, while i was waiting on a reply, i found this to work for me:

    =IF(Month1 = "1", "JAN", IF(Month1 = "2", "FEB", IF(Month1 = "3", "MAR", IF(Month1 = "4", "APR", IF(Month1 = "5", "MAY", IF(Month1 = "6", "JUN", IF(Month1 = "7", "JUL", IF(Month1 = "8", "AUG", IF(Month1 = "9", "SEP", IF(Month1 = "10", "OCT", IF(Month1 = "11", "NOV", IF(Month1 = "12", "DEC"))))))))))))


    I did check you string, i got INVALID DATA TYPE, but i think it would work, i may have not lined up the month@row correctly.


    I'm good to go tho and thanks again!

    Rick

  • Community Champion

    @Rick Girard

    Excellent!

    You're more than welcome!

    Pro-tip: I'd recommend formatting the Months with numbers, like 01 JAN, 02 FEB because if you need to sort and show it in a Report, it will be in the right order.

    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.

  • none of these worked

  • Community Champion

    Hi @khadijah

    I hope you're well and safe!

    Can you elaborate? What didn't work?

    Be safe, and have a fantastic weekend!

    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, Awesome, 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.

  • ✭✭✭✭

    Rick's formula worked for me when I modified it slightly:

    =IF([Month1]@row = "01", "JAN", IF([Month1]@row = "02", "FEB", IF([Month1]@row = "03", "MAR", IF([Month1]@row = "04", "APR", IF([Month1]@row = "05", "MAY", IF([Month1]@row = "06", "JUN", IF([Month1]@row = "07", "JUL", IF([Month1]@row = "08", "AUG", IF([Month1]@row = "09", "SEP", IF([Month1]@row = "10", "OCT", IF([Month1]@row = "11", "NOV", IF([Month1]@row = "12", "DEC"))))))))))))

  • ✭✭✭✭

    If you only need three digit month names the formula is much simpler:

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

  • I've tried a few times to use this and it will not work. any suggestions you see i'm doing wrong. I want the start month to = month name



  • ✭✭✭✭

    Heather,

    It looks like you have forgotten to append @row to the cell ref for [Start Month]


    It should look like

    ...[Start Month]@row * 3...

  • This worked for me, great formula, thank you for sharing!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions