Identify weekend that has the max number of occurrences

  • Undoubtedly, this particular problem is the absolute LOWEST priority of any calculations I need. I worked out an answer but it is again clunky and takes up a bunch of real estate.


    In a list of dates, I am looking for the max number of occurrences in any one weekend, defined as Fri., Sat., and Sun., and the dates of that weekend. The data contains duplicates but will always be chronological.


    In my clunky solution, I listed all of the possible weekends and then counted occurrences in each weekend. Is there a less caveman-like way of obtaining the same results? Ideally, I would like to eliminate columns C:E. Adding a helper column or two to the DATA would be acceptable.


    I would appreciate any help but please don’t lose any sleep, it isn’t all that important.


    Thanks!


    weekends.xlsx

  • How about

    =LET(w,WORKDAY.INTL(A3,SEQUENCE(NETWORKDAYS.INTL(A3,A69,"1111011")),"1111011"),c,COUNTIFS(A:A,">="&w,A:A,"<="&w+2),FILTER(CHOOSE({1,2,3},c,w,w+2),c=MAX(c)))

  • How about

    =LET(w,WORKDAY.INTL(A3,SEQUENCE(NETWORKDAYS.INTL(A3,A69,"1111011")),"1111011"),c,COUNTIFS(A:A,">="&w,A:A,"<="&w+2),FILTER(CHOOSE({1,2,3},c,w,w+2),c=MAX(c)))

    I am totally gobsmacked! I threw this problem out there believing that there could not be a solution. You nailed it! I thank you very much.


    Is it possible to tweak this most excellent formula a bit to include blank cells at the end to allow for future dates to be added? When I change A69 to A70, it gives me an error. I'm thrilled with the formula the way it is, but if it could include blank cells at the end it would be perfect. I realize now that I should have stated this in the original post. My bad.


    Again, I am most appreciative of your help!

  • Ok, how about

    =LET(w,WORKDAY.INTL(A3-1,SEQUENCE(NETWORKDAYS.INTL(A3,MAX(A3:A100),"1111011")),"1111011"),c,COUNTIFS(A:A,">="&w,A:A,"<="&w+2),FILTER(CHOOSE({1,2,3},c,w,w+2),c=MAX(c)))


    This will now work if the first date is a Friday, which it would ignore before.

  • Ok, how about

    =LET(w,WORKDAY.INTL(A3-1,SEQUENCE(NETWORKDAYS.INTL(A3,MAX(A3:A100),"1111011")),"1111011"),c,COUNTIFS(A:A,">="&w,A:A,"<="&w+2),FILTER(CHOOSE({1,2,3},c,w,w+2),c=MAX(c)))


    This will now work if the first date is a Friday, which it would ignore before.

    Beautiful! My workbooks are now 100% perfect. There is absolutely zero that can be improved upon. ;)


    Thanks again!

Participate now!

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