Simplify Formula

Cesar Perez

I have a formula that checks to see if a date falls between dates. If so, it marks the row current or previous. The goal is to see if the date falls in the current year or the previous year and then label the row accordingly. The formula references another sheet where the dates are calculated. It has a column for the start date for every month since 01/01/20 through 12/01/20. It also has another column for the end date starting with 01/31/20 through 12/31/21. I created the following formula with IF statements to validate the date.

=IF(AND(Date@row >= {JanStartPrevious}, Date@row <= {JanEndPrevious}), "PreviousJanuary", IF(AND(Date@row >= {FebStartPrevious}, Date@row <= {FebEndPrevious}), "PreviousFebruary", IF(AND(Date@row >= {MarStartPrevious}, Date@row <= {MarEndPrevious}), "PreviousMarch", IF(AND(Date@row >= {AprStartPrevious}, Date@row <= {AprEndPrevious}), "PreviousApril", IF(AND(Date@row >= {MayStartPrevious}, Date@row <= {MayEndPrevious}), "PreviousMay", IF(AND(Date@row >= {JunStartPrevious}, Date@row <= {JunEndPrevious}), "PreviousJune", IF(AND(Date@row >= {JulStartPrevious}, Date@row <= {JulEndPrevious}), "PreviousJuly", IF(AND(Date@row >= {AugStartPrevious}, Date@row <= {AugEndPrevious}), "PreviousAugust", IF(AND(Date@row >= {SepStartPrevious}, Date@row <= {SepEndPrevious}), "PreviousSeptember", IF(AND(Date@row >= {OctStartPrevious}, Date@row <= {OctEndPrevious}), "PreviousOctober", IF(AND(Date@row >= {NovStartPrevious}, Date@row <= {NovEndPrevious}), "PreviousNovember", IF(AND(Date@row >= {DecStartPrevious}, Date@row <= {DecEndPrevious}), "PreviousDecember", IF(AND(Date@row >= {JanStartCurrent}, Date@row <= {JanEndCurrent}), "CurrentJanuary", IF(AND(Date@row >= {FebStartCurrent}, Date@row <= {FebEndCurrent}), "CurrentFebruary", IF(AND(Date@row >= {MarStartCurrent}, Date@row <= {MarEndCurrent}), "CurrentMarch", IF(AND(Date@row >= {AprStartCurrent}, Date@row <= {AprEndCurrent}), "CurrentApril", IF(AND(Date@row >= {MayStartCurrent}, Date@row <= {MayEndCurrent}), "CurrentMay", IF(AND(Date@row >= {JunStartCurrent}, Date@row <= {JunEndCurrent}), "CurrentJune", IF(AND(Date@row >= {JulStartCurrent}, Date@row <= {JulEndCurrent}), "CurrentJuly", IF(AND(Date@row >= {AugStartCurrent}, Date@row <= {AugEndCurrent}), "CurrentAugust", IF(AND(Date@row >= {SepStartCurrent}, Date@row <= {SepEndCurrent}), "CurrentSeptember", IF(AND(Date@row >= {OctStartCurrent}, Date@row <= {OctEndCurrent}), "CurrentOctober", IF(AND(Date@row >= {NovStartCurrent}, Date@row <= {NovEndCurrent}), "CurrentNovember", IF(AND(Date@row >= {DecStartCurrent}, Date@row <= {DecEndCurrent}), "CurrentDecember"))))))))))))))))))))))))

My question is if there is a simpler way of validating the dates. I think the formula above has too many if statements.

Mark Cronk

The results will list the month number and not the month name, but does this work for you?

=IF(YEAR(date@row)=YEAR(today()), "Current"+MONTH(date@row), IF(year(date@row)=year(today())-1, "Previous"+month(date@row), ""))

Mark

Mark Cronk

The results will list the month number and not the month name, but does this work for you?

=IF(YEAR(date@row)=YEAR(today()), "Current"+MONTH(date@row), IF(year(date@row)=year(today())-1, "Previous"+month(date@row), ""))

Mark

Cesar Perez

That works Mark. I also modified your formula to get the actual month name.

=IF(YEAR(Date@row) = YEAR(TODAY()), "Current" + INDEX({Month}, MATCH(MONTH(Date@row), {MonthNumber})), IF(YEAR(Date@row) = YEAR(TODAY()) - 1, "Previous" + INDEX({Month}, MATCH(MONTH(Date@row), {MonthNumber}))))