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

[Stefan2495]

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 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!

[#2]

@Stefan2495 

Based on the information you've provided and the image, here's a formula you can use in Excel to calculate the number of weeks until an event occurs for a specific item, considering the current date:

Excel

=IF(WEEKNUM(B4)="", "", CEILING((WEEKNUM($A$1:$A$255) - WEEKNUM(B4)) / 52, 1) + WEEKNUM(TODAY()) - WEEKNUM(B4))

Explanation:

1. WEEKNUM(B4): This part checks if the cell containing the event name (e.g., B4) is empty. If empty, it returns "", otherwise, it proceeds with the calculation.
2. WEEKNUM($A$1:$A$255): This creates an array of week numbers from cells A1 to A255 in your timeline.
3. WEEKNUM(B4): This gets the week number of the event date (assuming it's in cell B4).
4. CEILING((WEEKNUM($A$1:$A$255) - WEEKNUM(B4)) / 52, 1): This calculates the number of years between the current week (from the timeline) and the event week, rounded up to the nearest whole year using CEILING. The division by 52 converts weeks to years.
5. **+ WEEKNUM(TODAY()): This gets the week number of today's date.
6. - WEEKNUM(B4): This subtracts the week number of the event date from the current week and the calculated years, providing the remaining weeks until the event.

How to use the formula:

1. Copy the formula and paste it into cells C4, C5, or any other cells where you want to display the remaining weeks for each item.
2. Adjust the cell references (e.g., B4, A1:A255) if your event names and timeline location differ.

Additional notes:

• This formula assumes your week numbers are in column A. If they're in a different column, adjust the references accordingly.
• You can adjust the range A1:A255 to cover the extent of your timeline data.
• This formula considers weeks within the current year. If events span multiple years, adjustments might be needed.

This formula should provide the desired number of weeks until each event, taking into account the current date and empty cells in the timeline.

[#3]

@Stefan2495 

=IFNA(FLOOR(INDEX($F$4:$V$4,MATCH(C$4,$F5:$V5,0))-TODAY(),7)/7,"")

This formula with INDEX and MATCH could be an alternative. The formula is in cell C4 and filled across range C4:D5.

[#4]

@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.