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)

Find more posts tagged with

Formulas

Accepted answers

All comments

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...

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.