I'm sure the array formula experts will have a far shorter solution, but
this should work:

="You hvave
"&INDEX(B1:B52;MATCH(VLOOKUP(TODAY();B1:B52;1;TRUE );B1:B52)+1)-TODAY()&"
days until deadline week
"&INDEX(A1:A52;MATCH(VLOOKUP(TODAY();B1:B52;1;TRUE );B1:B52)+1)

Error handling will lengthen it a lot (how much depending on excel version),
so I left that out. As is it needs a future date in B to work.

HTH. Best wishes Harald

"andrew" skrev i melding
...
I have 2 worksheets, one containing a summary to countdown while the other
contains 2 columns of the following (up to week 40):


Week Date
1 7-Jul
2 14-Jul
3 21-Jul
4 24-Jul
5 31-Jul

Is there a formula that i could use on a cell within the summary w'sheet
where it does a countdown to the planned dates as shown in table above?
I.e.

If today is 4th July, the formula cell will display 3 days to countdown.
If
today is 8th July (which means it has passed week 1), it will show 6 days
to
countdown. With this formula, i'm trying to display the following text:

"You have 3 days left to Week 1 deadline"

The formula will basically be inserted where 3 is, while Week 1 will be
inserted based on the condition of the countdown (not displaying the Date
column but rather the Week column number).

Is this possible?