Forum Discussion
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...
Hi,
I have applied my following formula to cell A10 (see screenshot above):
=LET(
data, A4:H7,
header, TAKE(data, 1),
body, DROP(data, 1),
drp, DROP(body,, 2),
iferr, IFERROR(drp / TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",,
BYCOL(drp, LAMBDA(a, ARRAYTOTEXT(
--BYROW((DATE(YEAR(a), 1, -2) - WEEKDAY(DATE(YEAR(a), 1, 3))
+ WEEKNUM(+a, 2) * 7 + {0,1,2,3,4,5,6}) = TODAY(), OR))))), ", ", ";")), 0),
byrw, BYROW(iferr, OR),
filter, FILTER(iferr, byrw),
HSTACK(VSTACK(TAKE(header,, 2), FILTER(TAKE(body,, 2), byrw)),
VSTACK(FILTER(DROP(header,, 2), BYCOL(iferr, OR)),
IFERROR(1 / (1 / FILTER(filter, BYCOL(filter, OR))), "")))
)
The date on my computer is in the format dd/mm/yyyy and the week starts on Monday.
Hope this helps.
IlirU