MONTH/YEAR formulas based on date column

Hi there,

I have a column for "Report Date", "Date received", and "Month/Year". In my "Month/Year" column, I want the report date to be written out instead of 1/6/21 as January 2021. However, sometimes our "Report Date" column is blank/has the word "Skip" written when we don't have to write a report for that row. When that happens, I want it to translate the "Date Received" column into a full written month and year.

I haven't been able to get the "Month/Year" column to write it out correctly. Can anyone help? Here is my formula that is currently filling in dates like 1/6/21 for the Skip rows.

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

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would strongly recommend creating a table with month numbers in one column and the text in another column.

    1.......January

    2.......February

    3.......March

    so on and so forth


    Then you can use a formula such as this...

    =INDEX([Text Column]:[Text Column], MATCH(MONTH(IF(ISDATE([Report Date]@row), [Report Date]@row, [Date Received]@row)), [Number Column]:[Number Column], 0))

  • Sophie Knudson
    Sophie Knudson ✭✭✭✭

    I'm not sure I'm following where this table would exist? In a separate sheet?

    How would I get the rows that have a blank or skip in the Report Date column to use the "January 2021" formatting when the Date Received column is a date in January?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could create the table in the same sheet or a different sheet.


    This portion is where it determines which date to use:

    IF(ISDATE([Report Date]@row), [Report Date]@row, [Date Received]@row)


    If the [Report Date] is an actual date, it will use that. If it is not a date (blank of "skip" or anything other than an actual date), it will use the date in the [Report Date] column.


    I did forget to tack on the YEAR portion though...


    =INDEX([Text Column]:[Text Column], MATCH(MONTH(IF(ISDATE([Report Date]@row), [Report Date]@row, [Date Received]@row)), [Number Column]:[Number Column], 0)) + " " + YEAR(IF(ISDATE([Report Date]@row), [Report Date]@row, [Date Received]@row))

  • Sophie Knudson
    Sophie Knudson ✭✭✭✭

    Huh I must not be doing the table how you are envisioning because I'm getting a #UNPARSEABLE error.


    Is there a reason this formula wouldn't work instead of the tables? Or any other guidance on how to successfully do the table?

    =IF(MONTH([Report date]@row) = 1, "January ",

    IF(MONTH([Report date]@row) = 2, "February ",

    IF(MONTH([Report date]@row) = 3, "March ",

    IF(MONTH([Report date]@row) = 4, "April ",

    IF(MONTH([Report date]@row) = 5, "May ",

    IF(MONTH([Report date]@row) = 6, "June ",

    IF(MONTH([Report date]@row) = 7, "July ",

    IF(MONTH([Report date]@row) = 8, "August ",

    IF(MONTH([Report date]@row) = 9, "September ",

    IF(MONTH([Report date]@row) = 10, "October ",

    IF(MONTH([Report date]@row) = 11, "November ",

    IF(MONTH([Report date]@row) = 12, "December "))))))))))

    + YEAR([Report date]@row))),

    IF(MONTH([Date received]@row) = 1, “January”,

    IF(MONTH([Date received]@row) = 2, “February”,

    IF(MONTH([Date received]@row) = 3, “March”,

    IF(MONTH([Date received]@row) = 4, “April”,

    IF(MONTH([Date received]@row) = 5, “May”,

    IF(MONTH([Date received]@row) = 6, “June”,

    IF(MONTH([Date received]@row) = 7, “July”,

    IF(MONTH([Date received]@row) = 8, “August”,

    IF(MONTH([Date received]@row) = 9, “September”,

    IF(MONTH([Date received]@row) = 10, “October”,

    IF(MONTH([Date received]@row) = 11, “November”,

    IF(MONTH([Date received]@row = 12, “December”))))))))))

    + YEAR([Date received]@row))))

  • Sophie Knudson
    Sophie Knudson ✭✭✭✭

    I guess a simpler way of stating this, is that I'm trying to create a formula that says...


    IF the "Report Date" column is a date, then enter the month and year written out in words (ex. January 2021) that was listed in the "Report date" cell (1/6/21).

    IF the "Report Date" column is NOT a date (blank, or "skip"), then enter the month and year written out in words (ex. January 2021) that was listed in the "Date received" cell (1/1/21).


    But I'm really struggling with the idea of the table and what that looks like on my sheet.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The table would look something like this...



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!