Forum Discussion

joannem7's avatar
joannem7
Copper Contributor
Nov 14, 2025

show a due date using data from multiple columns

I have a spreadsheet in excel, such as the Table 1 below. For the 5 Due Date columns, I have conditional formatting set up to format the cells. See Image 1 for a list of rules applied to all Due Date...
excel
Excel for web
Formulas and Functions
Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 16, 2025

Alternative with PIVOTBY

Credit due djclements​ (Unpivot/repivot option in Need help with excel | Microsoft Community Hub)

=LET(
  source,     TAKE(Table1[#All],, 7),
  body,       DROP(source, 1),
  today,      TODAY(),

  startWeek,  today - WEEKDAY(today, 2) +1,
  endWeek,    startWeek +6,
  dueValues,  DROP(body,, 2),
  arrDueThisWeek, (dueValues >= startWeek) * (dueValues <= endWeek),

  IF( SUM(arrDueThisWeek),
    LET(
      headers,    TAKE(source, 1),
      dueHeaders, DROP(headers,, 2),
      pvtRows,    TOCOL( IFS(arrDueThisWeek, SEQUENCE(ROWS(dueValues))), 2),
      HSTACK(
        VSTACK( TAKE(headers,, 2), CHOOSEROWS(TAKE(body,, 2), UNIQUE(pvtRows) ) ),
        DROP(
          PIVOTBY( pvtRows,
                   TOCOL( IFS(arrDueThisWeek, dueHeaders), 2),
                   TOCOL( IFS(arrDueThisWeek, dueValues), 2),
                   SINGLE,, 0,, 0
          ),,
          1
        )
      )
    ),
    VSTACK( "Comment", "Nothing due this week" )
  )
)

 

Resources