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?

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?

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?

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?

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?

Hi, the formula unfortunately is not working for the Week display now - its
showing Week 12 instead of Week 3 of the planned dates with corresponding
Week.

Any ideas?

"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?