Finding all the quarters a range of dates fall into using If then/and/or IfElse?

[VB4MEEE]

I have a spreadsheet with quarters listed along the top, there are tasks along the left hand side, within each task is a "start date" and a "max days"; "start date" + "max days" = "end date" (in other words, end date is not a date but a formula that returns a number. These tasks can last 14 days, or 360 days. Next to each task, under each quarter, I am to black out a cell IF the task will be worked on during that quarter.

I have created two "sub tables" to the right that return if the start date is within a quarter (easy) and when the end date is within a quarter (easy). The hard part is if the days being worked in are more than 60 and fall into quarter BETWEEN the start and end quarter. For the 360 example max time, above, there will be 4 cells blacked out (can could be 5 depending on the start/end date's relation to the quarter start/end) because that 360 day example will be worked on during four full quarters (and maybe a bit of one other on the start or the end). With my sub tables, I get the quarter that the task starts on, no problem. And, I can get the quarter that the task ends on, no problem. The problem is how in the world can I communicate to excel that if the task is worked on during any of these quarters in the middle (not the start or the end quarter), that excel should also return a "yes" (in this case an "X" and then conditional formatting blacks out the cell. Here is a bit of the example:


Duration 18Q3 18Q4 19Q1 19Q2 19Q3 19Q4 20Q1 20Q2 20Q3
Min Max D Start Date End Date 9/30 12/31 3/31 6/30 9/30 12/31 3/31 6/30 9/30

14 30 9/9/19 2019 10/8/19 X X
7 14 10/8/19 2019 10/21/19 X
14 240 10/21/19 2019 6/16/20 X X


In the above example, where max days are 240, I got an x in the 19Q4 and 20Q2 but not the 20Q1. This is the issue.

FYI, max days are in col I, Start Date in Col J, End Date in Col L, 18Q3 in Column M. This is a 5-year plan doc so dates go out to 23Q4.

[VB4MEEE]

Attachment 603161

The copy and paste from the sheet did not carry over format, here is a picture of that section.

[GeoffW283]

It will help a lot if you upload a small but representative subset of your workbook (not a screenshot) that illustrates the problem.

Remove any sensitive data first.

To attach a file click on “Go Advanced” then “Manage attachments”.

[VB4MEEE]

Did this work as an attachment?

[bebo021999]

I don't use "sub table" and column K that may be helper(?)
Try CF formula in cell M31:

Please Login or Register  to view this content.

Also, first column title, type 09/30/2018 (or any end quarter date), to get "yyQn" in M3 automatically then drag accross:

Please Login or Register  to view this content.

To get next quater end date, from N4 then drag accross:

Please Login or Register  to view this content.

[Bo_Ry]

Another one
CF formula apply to $M$31:$AH$39

=($J31<=M$4)*($L31>EOMONTH(M$4,-3))

Not sure how you want to display if start date is at the end of quarter, you may remove "=" sign

Year and Quater M3 copy to the right

=TEXT(M4,"yyQ")&INT((MONTH(M4)+2)/3)