Forum Discussion

Stefan2495's avatar
Stefan2495
Copper Contributor
Feb 20, 2024

EXCEL Deadline-tracker: Counting/ displaying weeks until you reach an event

Hi Community, 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 e...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 20, 2024

Stefan2495 

I wrote a formula that returns results for an entire table as a dynamic array.  It may not be precise in terms of counting weeks inclusive or exclusive and it also contains a fail.  It does not list weeks to go to an array of future events as an array, because that requires 'an array of arrays' which, for some technical reason that eludes me, Microsoft considers to be 'sinful'.

 

My formula is

 

= BYROW(events,
    LAMBDA(e,
      LET(
        futureEvent?, (dateLine>TODAY()) * ISTEXT(e),
        weeksToGo, WEEKNUM(FILTER(dateLine, futureEvent?)) - WEEKNUM(TODAY()),
        TEXTJOIN(", ", , weeksToGo)
      )
    )
  )

 

The formula only considers future dates (Event0 is filtered out) and the results are presented as an array of comma-separated lists.

Resources