Formula to indicate which quarter the Project's end date is in ...

James Wesley

… I had a formula that was working before July 1, but now "Incorrect Argument Set". :(

The purpose of this formula is to indicate where the project's end date is in the Past Quarter (one quarter behind the current quarter), the Current Quarter (the end date occurs within the current quarter we're in), and the Next Quarter (the end date occurs in the next quarter).

Oct 1, is the turn of the fiscal year, so it's Q1.

Thoughts?

=IF(OR([Number of days between today's and End date]@row > 274, [Number of days between today's and End date]@row < -274), "", IF([Helper - Today's Fiscal Quarter (#)]@row = 1, IF([Helper - Deadline Quarter (#)]@row = 4, "Past Quarter", IF([Helper - Deadline Quarter (#)]@row = 1, "Current Quarter", IF([Helper - Deadline Quarter (#)]@row = 2, "Next Quarter", ""))), IF([Helper - Today's Fiscal Quarter (#)]@row = 2, IF([Helper - Deadline Quarter (#)]@row = 1, "Past Quarter", IF([Helper - Deadline Quarter (#)]@row = 2, "Current Quarter", IF([Helper - Deadline Quarter (#)]@row = 3, "Next Quarter", ""))), IF([Helper - Today's Fiscal Quarter (#)]@row = 3, IF([Helper - Deadline Quarter (#)]@row = 2, "Past Quarter", IF([Helper - Deadline Quarter (#)]@row = 3, "Current Quarter", IF([Helper - Deadline Quarter (#)]@row = 4, "Next Quarter", ""))), IF([Helper - Today's Fiscal Quarter (#)]@row = 4, IF([Helper - Deadline Quarter (#)]@row = 3, "Past Quarter", IF([Helper - Deadline Quarter (#)]@row = 4, "Current Quarter", IF([Helper - Deadline Quarter (#)]@row = 1, "Next Quarter", "")), ""))))))

Adam Murphy

@James Wesley,

Try something like this:

=IF(YEAR([End Date]@row ) = YEAR(TODAY()), IF([Helper - Deadline Quarter (#)]@row = [Helper - Today's Fiscal Quarter (#)]@row , "Current Quarter", IF(ENDQTR@row = 1, "Future Quarter", IF([Helper - Deadline Quarter (#)]@row - [Helper - Today's Fiscal Quarter (#)]@row > 0, "Future Quarter", "Past Quarter"))), IF(YEAR(TODAY()) > YEAR([End Date]@row ), "Past Quarter", IF(YEAR(TODAY()) < YEAR([End Date]@row ), "Future Quarter", "")))

Does that work?