You won't be able to do that with a formula - you will need some VBA.

Pete

"andrew" wrote in message
...
Almost there. What if i want just the number in BOLD while the rest of the
text remains in regular format?

"Pete_UK" wrote:

Assuming your dates are in Sheet1, I would suggest that you insert a
new row 2, so that you have a table that looks like this:

Week Date
0 30-Jun
1 07-Jul
2 14-Jul
3 21-Jul
4 28-Jul

and so on. (Note, my dates are a bit different than yours !)

Then in your summary sheet you can put this formula:

="You have "&INDEX(Sheet1!B$2:B$42,MATCH(TODAY(),Sheet1!B$2:B $42)+1)-
TODAY()&" days left to Week "&MATCH(TODAY(),Sheet1!B$2:B$42)&"
deadline"

to give you what you want - this assumes dates down to row 42.

Hope this helps.

Pete



On Jul 4, 9:57 am, andrew wrote:
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?