Simplify Formula

Options

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.


Tags:

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Cesar Perez
    Cesar Perez ✭✭✭✭
    Options

    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}))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!