Search code examples
arraysdategoogle-sheetsarray-formulasgoogle-query-language

In Google Sheets, how can I get todays date and the previous 19 days in a list. Excluding Weekends and even Holidays if possible?

I start with =today() in the first cell which works fine. And then use =A2-IF(WEEKDAY(A2)<5,3,1) which I found on the internet but is giving me wrong dates.

Solution

  • getting 19 days:

    =INDEX(TEXT(SEQUENCE(19, 1, TODAY()-18), "dd/mm/yyyy"))

    enter image description here

    excluding weekends from those 19 days:

    =INDEX(QUERY(TEXT(SEQUENCE(19, 1, TODAY()-18), {"dd/mm/yyyy", "ddd"}), 
 "where not Col2 matches 'Sat|Sun'"),, 1)

    enter image description here

    adding a list of holidays which should be excluded too:

    =INDEX(QUERY(TEXT(SEQUENCE(19, 1, TODAY()-18), {"dd/mm/yyyy", "ddd"}), 
 "where not Col2 matches 'Sat|Sun'
    and not Col1 matches '"&TEXTJOIN("|", 1, D2:D)&"'"),, 1)

    enter image description here

    getting 19 entries after excluding weekends and holidays:

    =INDEX(SORT(QUERY(TEXT(SEQUENCE(50, 1, TODAY()-49), 
 {"dd/mm/yyyy", "ddd", "@"}), 
 "where not Col2 matches 'Sat|Sun'
    and not Col1 matches '"&TEXTJOIN("|", 1, D2:D)&"'
  order by Col3 desc 
  limit 19"), 3, 1),, 1)

    enter image description here

    if you for some reason need valid dates use:

    =INDEX(TO_DATE(1*SORT(QUERY(TEXT(SEQUENCE(50, 1, TODAY()-49), 
 {"dd/mm/yyy", "ddd", "@"}), 
 "where not Col2 matches 'Sat|Sun'
    and not Col1 matches '"&TEXTJOIN("|", 1, D2:D)&"'
  order by Col3 desc 
  limit 19"), 3, 1)),, 1)

    enter image description here