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.
