How to convert Date (MM/DD/YYYY) into Month and Quarters and Year such as "Q1 2020" and "Jan 2020"

How to convert Date (MM/DD/YYYY) into Month and Quarters and Year such as "Q1 2020" and "Jan 2020"

Best Answer

  • Anil Rattan
    Anil Rattan ✭✭
    Answer ✓

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

    This one worked

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To convert to "Q# yyyy" you would use something along the lines of...

    ="Q" + IF(MONTH([Date Column]@row) >= 10, "4 ", IF(MONTH([Date Column]@row) >=7, "3 ", IF(MONTH([Date Column]@row) >=4, "2 ", "1 "))) + YEAR([Date Column]@row)


    To convert to "Mon yyyy", you could either build out a table where the month number is listed in one column and the desired month text is in another and then use an INDEX/MATCH or VLOOKUP along the lines of

    =INDEX([Month Text]:[Month Text], MATCH(MONTH([Date Column]@row), [Month Number]:[Month Number], 0))


    or you could use a nested IF statement for each month such as

    =IF(MONTH([Date Column]@row) = 1, "Jan ", IF(MONTH([Date Column]@row) = 2, "Feb ", ................................................................................................))))))))))) + YEAR([Date Column]@row)

  • Anil Rattan
    Anil Rattan ✭✭
    Answer ✓

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

    This one worked

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!