Formula to correctly assign a quarter (e.g. Oct-Dec) to a due date

NTDSC

Hello!

I have a sheet that lists project milestones with a column for the milestone's due date and another column that I want to automatically assign the quarter that that due date falls in (Jan-Mar, Apr-Jun, Jul-Sep, Oct-Dec, and Q5 - which represents the "current" quarter but next year). Then there are projections columns for each quarter that pull in the payment amounts based on these designations.

It's working mostly correctly, but I had to make significant edits to it when it changed from September to October (a new quarter) because it suddenly assigned the same quarter to almost every milestone regardless of date, so I am worried the same will happen in future quarters, and especially when it becomes a new calendar year.

In addition to just generally checking that the formula is doing what I need it to do and will do so regardless of the current date... Here are my other specific problems:

1. If the due date is in the past and in a past quarter (e.g. due 9/30/23, and it is now 10/5/23), and not marked complete yet, the quarter should read "PAST" so that the project manager can update the date. If the milestone due date is in the past, but still in the current quarter (e.g. if it was due 10/2/23 and it is now 10/5/23) I would like it to still be assigned the current quarter. Right now, it only says "PAST" if the date is in the past at all, regardless of quarter.
2. The part that calculates Q5 is still having some trouble... Right now the only error I see in the sheet is that it assigned 8/30/24 as Q5 when it should just be Jul-Sep, and I can't figure out why. I am sure that part of the formula is a total mess though, I'm the one that created it and I can't really tell what it's doing.

(see screenshot below)

The calculation needs to incorporate whatever today's date/year is, mainly because of the need for Q5, which shifts every 3 months (it can't just say "if the date is between 7/1 and 9/30 then it's Jul-Sep" because if it's in those dates but next year then it needs to say Q5)... And it needs to somehow use the start/end dates of each quarter as boundaries to pinpoint where the due date falls.

This is the current column formula:

=IF(AND(ISDATE([Milestone Due Date]@row), [Milestone Complete?]@row = 0), IF([Milestone Due Date]@row < TODAY(), "PAST", IF([Milestone Due Date]@row <= (IF(TODAY() > DATE(YEAR(TODAY()), 9, 30), DATE(YEAR(TODAY() + 1), 9, 30), DATE(YEAR(TODAY()), 9, 30))), "Jul-Sep", IF([Milestone Due Date]@row <= (IF(TODAY() > DATE(YEAR(TODAY() - 1), 12, 31), DATE((YEAR(TODAY()) + 1), 12, 31), DATE(YEAR(TODAY()), 12, 31))), "Oct-Dec", IF([Milestone Due Date]@row <= (IF(TODAY() > DATE(YEAR(TODAY()), 3, 31), DATE((YEAR(TODAY()) + 1), 3, 31), DATE(YEAR(TODAY()), 3, 31))), "Jan-Mar", IF([Milestone Due Date]@row <= (IF(TODAY() > DATE(YEAR(TODAY()), 6, 30), DATE((YEAR(TODAY()) + 1), 6, 30), DATE(YEAR(TODAY()), 6, 30))), "Apr-Jun", IF([Milestone Due Date]@row <= (IF(AND((TODAY()) > DATE(YEAR(TODAY()), 3, 31), (TODAY()) < DATE(YEAR(TODAY()), 7, 1)), DATE((YEAR(TODAY()) + 1), 6, 30), IF(AND((TODAY()) > DATE(YEAR(TODAY()), 6, 30), (TODAY()) < DATE(YEAR(TODAY()), 10, 1)), DATE((YEAR(TODAY()) + 1), 9, 30), IF(AND((TODAY()) > DATE(YEAR(TODAY()), 9, 30), (TODAY()) <= DATE(YEAR(TODAY()), 12, 31)), DATE((YEAR(TODAY()) + 1), 12, 31), IF(AND((TODAY()) >= DATE(YEAR(TODAY()), 1, 1), (TODAY()) < DATE(YEAR(TODAY()), 4, 1)), DATE((YEAR(TODAY()) + 1), 3, 31)))))), "Q5", "")))))))

I am sure there is a glaring error in how I set this up, but I'm hoping it's straightforward enough to fix, since I have looked at it for way too many hours now and I am all out of ideas.

Thank you all in advance!

Genevieve P.

Hey @NTDSC

So there's definitely a way to do it all in one massive formula, but my head is spinning and I personally needed to break it down into smaller pieces.

I have 3 helper columns, and a final column.

• 1 Column to simply return the Months
• 1 Column to return Q5 based on today's date
• 1 Column to return "Past" or "Future" based on today's date
• Final column that prioritizes what to return

Colours in my sheet just to show you how I pulled it all into one final formula:

Column 1: Quarter

Great! Pretty straightforward (albeit a bit long). Look at the month and if it's one of three options for each quarter, return that quarter in text.

=IF(AND(ISDATE([Milestone Due Date]@row), [Milestone Complete?]@row = 0),

IF(OR(MONTH([Milestone Due Date]@row) = 1, MONTH([Milestone Due Date]@row) = 2, MONTH([Milestone Due Date]@row) = 3), "Jan-Mar",

IF(OR(MONTH([Milestone Due Date]@row) = 4, MONTH([Milestone Due Date]@row) = 5, MONTH([Milestone Due Date]@row) = 6), "Apr-Jun",

IF(OR(MONTH([Milestone Due Date]@row) = 7, MONTH([Milestone Due Date]@row) = 8, MONTH([Milestone Due Date]@row) = 9), "Jul-Sept",

IF(OR(MONTH([Milestone Due Date]@row) = 10, MONTH([Milestone Due Date]@row) = 11, MONTH([Milestone Due Date]@row) = 12), "Oct-Dec")))))

Column 2: Q5

A little trickier, but uses the same principles. If the Today's Month is one of these three months, and the due date is greater than the start month for today's quarter next year, but less than the end month for today's quarter next year, then it's Q5.

=IF(AND(ISDATE([Milestone Due Date]@row), [Milestone Complete?]@row = 0),

IF(OR(

AND(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3), [Milestone Due Date]@row >= DATE(YEAR(TODAY()) + 1, 1, 1), [Milestone Due Date]@row < DATE(YEAR(TODAY()) + 1, 4, 1)),

AND(OR(MONTH(TODAY()) = 4, MONTH(TODAY()) = 5, MONTH(TODAY()) = 6), [Milestone Due Date]@row >= DATE(YEAR(TODAY()) + 1, 7, 1), [Milestone Due Date]@row < DATE(YEAR(TODAY()) + 1, 4, 1)),

AND(OR(MONTH(TODAY()) = 7, MONTH(TODAY()) = 8, MONTH(TODAY()) = 9), [Milestone Due Date]@row >= DATE(YEAR(TODAY()) + 1, 7, 1), [Milestone Due Date]@row < DATE(YEAR(TODAY()) + 1, 10, 1)),

AND(OR(MONTH(TODAY()) = 10, MONTH(TODAY()) = 11, MONTH(TODAY()) = 12), [Milestone Due Date]@row >= DATE(YEAR(TODAY()) + 1, 10, 1), [Milestone Due Date]@row < DATE(YEAR(TODAY()) + 2, 1, 1))),

"Q5"))

Column 3: Past or Future

Similar to Q5, we need to check today's quarter (today's month) to be able to figure this one out. If Today's Month is in these specific months, but the milestone date is less than the beginning of this quarter, return "Past".

Otherwise, if the Milestone date is more than a year in the future (365 days) and the Q5 row is blank, then it's in the future. (This is where it's helpful having them be in different columns)

=IF(AND(ISDATE([Milestone Due Date]@row), [Milestone Complete?]@row = 0),

IF(OR(

AND(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 3), [Milestone Due Date]@row <= DATE(YEAR(TODAY()) - 1, 12, 31)),

AND(OR(MONTH(TODAY()) = 4, MONTH(TODAY()) = 5, MONTH(TODAY()) = 6), [Milestone Due Date]@row < DATE(YEAR(TODAY()), 4, 1)),

AND(OR(MONTH(TODAY()) = 7, MONTH(TODAY()) = 8, MONTH(TODAY()) = 9), [Milestone Due Date]@row < DATE(YEAR(TODAY()), 7, 1)),

AND(OR(MONTH(TODAY()) = 10, MONTH(TODAY()) = 11, MONTH(TODAY()) = 12), [Milestone Due Date]@row < DATE(YEAR(TODAY()), 10, 1))), "PAST",

IF(AND([Milestone Due Date]@row > TODAY(365), [Q5]@row = ""), "FUTURE")))

Column 4: Final Formula

We've done all the heavy lifting in the other 3. Now it's just about prioritizing what to return... Past first, then Q5, THEN and only then do we return the Months.

=IF([Past or Future]@row <> "", [Past or Future]@row, IF([Q5]@row <> "", [Q5]@row, Quarter@row))

Let me know if you're able to add the extra columns into your sheet. If not, we can work on a way of combining this... but it may be more difficult to maintain if any changes need to happen in the future.

Cheers,

Genevieve