How To Extract Month And Year Only From A Date and if the date column is blank Return Blank?

Hi All,

I have to generate a monthwise report to show how many Requests are in a particular month I tried the following formula. I have also added IF(ISBLANK()""), so that it leaves the cell blank if the date is not there in the [Request Received Date] column. One it is not considering the Year and well as throwing #INVALID OPERATION error.

=IF(ISBLANK([Request Received Date]@row), "", IF(MONTH([Request Received Date]@row) = 1, "January", IF(MONTH([Request Received Date]@row) = 2, "February", IF(MONTH([Request Received Date]@row) = 3, "March", IF(MONTH([Request Received Date]@row) = 4, "April", IF(MONTH([Request Received Date]@row) = 5, "May", IF(MONTH([Request Received Date]@row) = 6, "June", IF(MONTH([Request Received Date]@row) = 7, "July", IF(MONTH([Request Received Date]@row) = 8, "August", IF(MONTH([Request Received Date]@row) = 9, "September", IF(MONTH([Request Received Date]@row) = 10, "October", IF(MONTH([Request Received Date]@row) = 11, "November", IF(MONTH([Request Received Date]@row) = 12, "December")))))))))))))

Please Help!

Best Answer

  • Mindfull
    Mindfull ✭✭✭✭✭
    Answer ✓

    Hi Paul,

    It is resolved I used following

    =IFERROR(IF(MONTH([Request Received Date]@row) = 1, "January", IF(MONTH([Request Received Date]@row) = 2, "February", IF(MONTH([Request Received Date]@row) = 3, "March", IF(MONTH([Request Received Date]@row) = 4, "April", IF(MONTH([Request Received Date]@row) = 5, "May", IF(MONTH([Request Received Date]@row) = 6, "June", IF(MONTH([Request Received Date]@row) = 7, "July", IF(MONTH([Request Received Date]@row) = 8, "August", IF(MONTH([Request Received Date]@row) = 9, "September", IF(MONTH([Request Received Date]@row) = 10, "October", IF(MONTH([Request Received Date]@row) = 11, "November", IF(MONTH([Request Received Date]@row) = 12, "December")))))))))))) + " " + YEAR([Request Received Date]@row), "")

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Do you NEED to have the month in text, or could you use something along the lines of "07/20" for July of 2020?

  • Mindfull
    Mindfull ✭✭✭✭✭
    Answer ✓

    Hi Paul,

    It is resolved I used following

    =IFERROR(IF(MONTH([Request Received Date]@row) = 1, "January", IF(MONTH([Request Received Date]@row) = 2, "February", IF(MONTH([Request Received Date]@row) = 3, "March", IF(MONTH([Request Received Date]@row) = 4, "April", IF(MONTH([Request Received Date]@row) = 5, "May", IF(MONTH([Request Received Date]@row) = 6, "June", IF(MONTH([Request Received Date]@row) = 7, "July", IF(MONTH([Request Received Date]@row) = 8, "August", IF(MONTH([Request Received Date]@row) = 9, "September", IF(MONTH([Request Received Date]@row) = 10, "October", IF(MONTH([Request Received Date]@row) = 11, "November", IF(MONTH([Request Received Date]@row) = 12, "December")))))))))))) + " " + YEAR([Request Received Date]@row), "")

  • Thanks for this code it helps me a lot. Is there a way to set the code for the entire column instead of dragging the corner and pulling it down?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Larry W

    Yes! If you right-click on the cell that contains the formula, you can choose an option to "Convert to Column Formula"

    See: Set Formulas for All Rows with Column Formulas

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!