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
Nov 14, 2025

Hi

  • If you want something that follows exactly your Cond. Format. rules, VBA is required - not an option with Excel Web (your tag)
  • With weeks starting on Monday (as below), 11/17/2025 is next week, not this week. If your week doesn't start on Monday the formula must be adjusted, line:
 startWeek,        today - WEEKDAY(today,2) +1,

 

With data in Table1 (blue table), in B9 a dynamic array with formula:

=LET(
  source,           TAKE( Table1[#All],, 7 ),
  headers,          TAKE( source, 1),
  body,             DROP( source, 1 ),
  pivotColums,      CHOOSECOLS( body, {1,2} ),
  dueColumns,       CHOOSECOLS( body, {3,4,5,6,7} ),
  today,            TODAY(),
  startWeek,        today - WEEKDAY(today,2) +1,
  endWeek,          startWeek +6,
  dueThisWeek,      (dueColumns >= startWeek ) * (dueColumns <= endWeek),
  hidedOtherDates,  IF( dueThisWeek, dueColumns, ""),
  keepRows,         MMULT(dueThisWeek, SEQUENCE(COLUMNS(dueThisWeek),,,0)),
  keepColumns,      HSTACK( {1,1}, MMULT(SEQUENCE(,ROWS(dueThisWeek),,0), dueThisWeek) ),
  toFilter,         HSTACK( pivotColums, hidedOtherDates ),
  filteredRows,     FILTER( toFilter, keepRows ),
  FILTER( VSTACK( headers, filteredRows ), keepColumns )
)

Corresponding sample avail. at:
https://1drv.ms/x/c/1cd824d35610aacb/ETFnFnBpzqtAsluZRtXKMA0B67yD_JXe36wVA4cVtLlLxw?e=cCi57I

Resources