Help with Determining if date@row occurred last month using multiple criteria.

Hi. I'm looking for some help with a formula.

I'm using the following formula to identify if the date last touched occurred last month, which works perfectly, but it was not my formula (which is why I'm having problems)…

=IFERROR(IF(AND(MONTH(TODAY()) = 1, YEAR(TODAY()) - YEAR([Last Touched]@row) = 1, (MONTH(TODAY()) - MONTH([Last Touched]@row) = -11)), "Previous Month", IF(YEAR(TODAY()) = YEAR([Last Touched]@row), IF(MONTH(TODAY()) - MONTH([Last Touched]@row) = 1, "Previous Month", "-"), "-")), "-")

I'm trying to create an IF OR formula where if the date in the "Last Touched" column occurred last month OR if the date in the "RM Close Date" occurred last month, then answer "Previous Month"; otherwise, answer "_."

Since I don't understand the formula I'm starting with, I'm having trouble adding the OR statement. Can anyone help me?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

I see what the formula is doing, but I would approach it a bit differently.

=IF(AND(MONTH([Last Touched]@row) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), YEAR([Last Touched]@row) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)), "Previous Month", "_")

We get the first of the current month and subtract one day from it to get the last day of the previous month. Then we can use a MONTH and YEAR function to get the month and year numbers for the previous month. If those match the month and year numbers from last touched, then we output "Previous Month".

Having it like this allows us to expand this to multiple columns by copying the full AND statement, adjusting the column name, and pasting it into an OR statement.

AND(MONTH([RM Close Date]@row) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), YEAR([RM Close Date]@row) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1))

OR(AND(………………), AND(………………..))

Jackie Bhamornsiri

@Paul Newcome

I appreciate your recommendation to change the base formula. My question is this: When I try to mesh the IF(OR(AND formula together, I get an "incorrect argument" error. I went back to double-check my parentheses, but I need help finding my mistake. Do you see anything obvious?

=IF(OR(AND(MONTH([Last Touched]@row) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1)- 1), YEAR([Last Touched]@row) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)), IF(OR(AND(MONTH([DM Close Date]@row) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), YEAR([DM Close Date]@row) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))), "Previous Month", "_")

Paul Newcome

Give this a try:

=IF(OR(AND(MONTH([Last Touched]@row) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), YEAR([Last Touched]@row) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)), AND(MONTH([DM Closed Date]@row) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), YEAR([DM Closed Date]@row) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1))), "Previous Month", "_")

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.