EXCEL Deadline-tracker: Counting "distance" of empty cells (in a row) until you reach an event

Question

I want to display the number of weeks it takes until an event is happening for a certain Item (B4, B5, etc.). My timeline is in weeks on the x-axis increasing to the right. There are empty cells in the row until i.e. "event 1" is reached.

I want to display the weeks it takes until a that "event 1" is reached from today (in C4, C5 or D4, D5). It would be perfect if excel can refer to the actual current date the file is opened.

Either by counting the empty cells from the current week or by a formula that refers to the week it takes place and then counts backwards with the dates.

Does anyone have a straightforward solution. Would be really thankful!

Answer 1

Here is one way of doing this, if i have understood correctly, then this should work accordingly as per the required request:

---

• Formula used in cell C4

=LET(α, $F4:$BE4, IFERROR(COLUMNS(DROP(TAKE(α,,XMATCH(1,1/(α=C$3))),,
                  XMATCH(1,1/(WEEKNUM(+$F$3:$BE$3)=WEEKNUM(TODAY())))+1)),0))

---

• Using the first XMATCH() returns the position of Event 1
• Using TAKE() to extract the number columns required.
• Using the second XMATCH() with WEEKNUM() function to get the position of the present weeks number.
• Using DROP() to exclude the number columns not required.
• Lastly, using COLUMNS() function to get the counts.
• Now, fill down and fill right.

---

Shorter & Quicker Version: Using TOROW() & TOCOL() ^{MS365 Exclusive}

---

=TOROW(WEEKNUM(+F$3:BE$3)/(F4:BE4=TOCOL(C$3:D$3)),3)-(WEEKNUM(TODAY())+1)

---

Answer 2

Another solution - put the formula in C4:

=LET(event,C$3,
tasks,$E4:$U4,
weeks,$E$3:$U$3,
dateEvent,XLOOKUP(event,tasks,weeks,0,0),
IF(dateEvent > TODAY(),(dateEvent -TODAY()+WEEKDAY(TODAY(),2) - 1)/7,""))

You can drag it to the left and down to get the other results