Less clunky way of listing missing month/day combinations

  • Greetings-


    I am looking for a simpler solution to listing the month/day combinations that are not present in a long list of dates. In this example there are 900 dates (dataN); they are in order and there are many duplicates. For instance, the first result is January 4, because there is no January 4 in any of the dataN dates. I need to list these month/days.


    I did work out a solution but I’m not convinced that it is the best or simplest. Column C is a complete list of possible month/days. I used 2020, the last leap year. Column D compares the month/day against dataN. If there are any matches, it returns nothing. If there are no matches, it returns that date. Column F is a unique list of the positive results in Column D. The years are totally irrelevant.


    I would appreciate any Excel ninja magic you all can come up with.


    dates.xlsx

  • How about

    =LET(a,TEXT(SEQUENCE(366,,DATE(2020,1,1)),"m/d"),FILTER(a,ISNA(MATCH(a,TEXT(dateN,"m/d"),0))))

    Obviously, Excel ninja magic does exist because I asked for some and that is exactly what I received. Thank you very much!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!