Hello! Struggling with formula/function syntax... I'm trying to get a count of projects that fall within a calendar year (i.e. had an active contract at any point during the year) using the start & end date of their contracts. Essentially, if the contract start date OR the contract end date is on or after 1/1 and on or before 12/31, it falls within that year. So the criteria would be EITHER ({Contract Start Date} >= 1/1/23 AND {Contract Start Date} <= 12/31/23) OR ({Contract End Date} >= 1/1/23 AND {Contract End Date} <= 12/31/23) .... But I can't figure out how to get that in a formula.
The following is basically half of what I need:
=COUNTIF({Contract Start}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31)))
This just gets me just the ones that started within 2023. Using the same exact formula but with Contract End as the reference would get the ones that ended within 2023.
But I can't start COUNTIF with "OR" so that it's looking at Start OR End... and I can't use OR on the criteria part of the formula because that's where I need AND (it's 2 different ranges but the same criteria for both)... and I can't start the whole formula with OR to do two different COUNTIF functions, since OR has to be nested within another function... And COUNTIFS would only get me the ones that started AND ended in 2023. I tried combining COUNT with COLLECT but that didn't work either, even when I changed the range (I thought maybe it didn't want to collect dates for a count so I changed it to look at a text field instead).
Thanks in advance for your help!
Leslie
