Simplify Formula
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.
Best Answer
-
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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!