9 votes

Posted by unknown user
Topic deleted by author

1 comment

  1. tomf
    (edited )
    Link
    Pardon the formatting, but if you're using Sheets and want to knock this out with one formula, here's one way to do it. ... I should be sleeping. With a range of A2:G, you can use...

    Pardon the formatting, but if you're using Sheets and want to knock this out with one formula, here's one way to do it.

    ... I should be sleeping.

    With a range of A2:G, you can use

    =AND(LEN(A2),MONTH(A2)=MONTH(TODAY()))

    then in a second condition,

    =AND(LEN(A2),ISODD(MONTH(A2)))

    to highlight every odd month, which makes it easier to navigate.

    edit: ok, it's there... super gnarly with lots of extra brackets, awful formatting, etc etc.

    =ARRAYFORMULA({
  "SUN","MON","TUE","WED","THU","FRI","SAT";
  IF(
   YEAR(
    DATE(YEAR(TODAY()),1,1)+
    SEQUENCE(
     ISOWEEKNUM(
      DATE(YEAR(TODAY()),12,31)),
      7,1)-
    WEEKDAY(DATE(YEAR(TODAY()),1,1)))<>
   YEAR(TODAY()),,
   DATE(
    YEAR(TODAY()),1,1)+
   SEQUENCE(
    ISOWEEKNUM(
     DATE(YEAR(TODAY()),12,31)),7,1)-
    WEEKDAY(DATE(YEAR(TODAY()),1,1)))})

    the months (in A2)

    =ARRAYFORMULA(
  IF(
   TEXT(
    INDEX(
     TRANSPOSE(
      QUERY(
       TRANSPOSE(
        VALUE(TEXT(B2:H,"DD")+1)),
       "select "&JOIN("),","min(Col"&row(indirect("A1:A"&count(B2:B))))&")")),,2),"D")="1", 
    TEXT(
      DATE(
       YEAR(TODAY()),
       INDEX(
        TRANSPOSE(
         QUERY(
          TRANSPOSE(
           VALUE(TEXT(B3:H,"MM"))),
          "select "&join("),","MAX(Col"&row(indirect("A1:A"&count(B3:B))))&")")),,2),
       1),
      "MMMM"),))

    demo that nobody will look at

    5 votes