I have this formula that I made that takes a range from a cell (in this case, 10/5 - 10/11), subtracts the dates from that range from a larger list of all remaining days in the year, giving me a list of all dates remaining in the year EXCEPT those between 10/5 and 10/11.

Here is the formula:

=ArrayFormula(FILTER(fullyr!A:A,ISERROR(match(fullyr!A:A,ARRAYFORMULA(TO_DATE(SEQUENCE(RIGHT(C2, SEARCH(" - ",C2))-LEFT(C2, SEARCH(" - ",C2)) +1,1,LEFT(C2, SEARCH(" - ",C2))))),0))))

So now what I'm hoping to be able to do, is have this formula reference multiple cells instead of just one and combine the ranges. So for example, it would reference cells B2, C2 & D2 which would be 9/20 - 10/5, 10/11 - 10/15 & 10/22 - 10/30

Any ideas?

  • Hi and welcome. Thanks for including your formula. However, with the greatest respect, your question is difficult to understand without seeing it in the context of some data, and also without being about to visualise what you mean when you say multiple cells instead of just one and _ combine the ranges_. Would you please edit your question to include some data (relevant to your formula) AND an example of how a successful outcome would appear. You could include snapshots, or (preferably) a shared spreadsheet. If your data is confidential, then please replace it with dummy data.
    – Tedinoz
    Sep 29 at 0:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.