Convert date field to month text

Hello!

My goal: 01/02/2024 returns a value of January or January 2024 in another cell

The column that contains 01/02/2024 is a formula calculating this date. I need to show the month (and year if possible) in text in another column.

Is this even possible?

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 08/07/23

    @Deanna Copello Because Smartsheet does not have the selection natively in the Date dropdown for Month Year, you will need to create a long formula.

    For the long formula it would be:

    if(Month([COLUMN NAME]@row) = 1, Join("January"," ",YEAR([COLUMN NAME]@row)),if(Month([COLUMN NAME]@row) = 2, Join("February"," ",YEAR([COLUMN NAME]@row)),etc.)

    You could also use a reference sheet with 2 columns, 1 being month number and 1 being month name, and then use an index/match

    =JOIN(INDEX({Month Number},MATCH(MONTH[DATE]@row,{MONTH NAME},0)," ",YEAR([DATE]@row))

  • What am I doing wrong here?


  • If i change the 1 to read 01 or 02, it also comes back with unparseable.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 08/07/23

    Hi @Deanna Copello

    I hope you're well and safe!

    There are multiple issues, but the proper structure depends on if it's in the same sheet or another.

    Is it all on the same sheet?

    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, 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:[email protected] | P: +46 (0) - 72 - 510 99 35

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

  • It is all on the same sheet in the same cell. the two columns pictured above are right next to each other. I did try the reference to another "source sheet" but that didn't work either.

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 08/07/23

    @Deanna Copello

    I just recreated your sheet, but I took the Join command out this time, which works.

    =IF(MONTH([Mike 1]@row) = 1, "January" + " " + YEAR([Mike 1]@row))

    If you want to add more months, then continue with nesting if statements.

    =IF(MONTH([Mike 1]@row) = 1, "January" + " " + YEAR([Mike 1]@row),IF(MONTH([Mike 1]@row) = 2, "February" + " " + YEAR([Mike 1]@row)))

  • AMAZING! Thank you SO much! you have no idea how happy the execs are going to be about this!!!!

  • I needed the same thing for merging into a generated document (so instead of 1/2 it would say January 2nd). I use a separate sheet to list numbers down one column. Then subsequent columns could be the months, the dates spelled out (first, second, etc) or even year spelled (twenty nineteen). Then you would have a separate column for Month@row so it only pulls the month.


    It'd be the date column, month calculation column, and vlookup column. Your date would be 1/2, the month column would be =month(date@row). Your vlookup column would be =vlookup([Month Calculation]@row,{{separate numbers smartsheet}}, 2,false).


    Hopefully that makes sense and is helpful.

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

    @Deanna Copello

    Happy to help!

    I saw that James had answered already!

    I'd recommend using the following structure to ensure that it will be in the correct order if you report on it.

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

    Let me know if I can help with anything else!

    Best,

    Andrée

    Remember! 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:[email protected] | P: +46 (0) - 72 - 510 99 35

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!