MONTH/YEAR formulas based on date column

Sophie Knudson

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)

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

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

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

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

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

The table would look something like this...