formula to show month and year based off a date xx/xx/xx

Hi I haven't seen anything in the questions already so am hoping to get a quick answer here. I have a column named receipt date and am needing to see month and year based off of the receipt date in a separate column named mo/yr. Is it possible to create a formula for this?

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @gmb-nps-xofigo88916

    You have an extra column in the Year function

    Try this

    =IF(LEN(MONTH([REQUESTED SHIPMENT DATE]@row)) = 2, MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row), 2), "0" + MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row), 2))

    Does that help?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 07/08/21

    Hi @gmb-nps-xofigo88916

    Yes, we can create a text field that appears in the format you described. Your mo/yr column should be formatted a text/number column

    If the leading zero for the month isn't important, the equation is:

    =MONTH([receipt date]@row) + "/" + RIGHT(YEAR([receipt date]@row), 2)

    *be sure I got the name of your date field correct

    If the leading zero of the Month is important, then this formula should work

    =IF(LEN(MONTH([receipt date]@row)) = 2, MONTH([receipt date]@row) + "/" + RIGHT(YEAR([receipt date]@row[receipt date]@row), 2), "0" + MONTH([receipt date]@row) + "/" + RIGHT(YEAR([receipt date]@row), 2))

    If you want the year to be 4 characters then omit the Right function.

    =MONTH([receipt date]@row) + "/" + YEAR([receipt date]@row)

    cheers,

    Kelly

  • Melanie Sanders
    Melanie Sanders ✭✭✭✭✭

    Hi Kelly,

    I used this formula as per what you had above provided for xx/xx/xx and it is unparseable. Here is what I used as the column where the date is actually REQUESTED SHIP DATE.

    =IF(LEN(MONTH([REQUESTED SHIPMENT DATE]@row)) = 2, MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row[REQUESTED SHIPMENT DATE]@row), 2), "0" + MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row), 2))

    Do you have any other suggestions?

    Thanks so much,

    Melanie

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @gmb-nps-xofigo88916

    You have an extra column in the Year function

    Try this

    =IF(LEN(MONTH([REQUESTED SHIPMENT DATE]@row)) = 2, MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row), 2), "0" + MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row), 2))

    Does that help?

    Kelly

  • Melanie Sanders
    Melanie Sanders ✭✭✭✭✭

    You are awesome....thank you so much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!