Working with dates

[andrew]

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?

 

[Harald Staff]

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

 

[Pete_UK]

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

 

[andrew]

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

 

[Pete_UK]

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

Pete

 

[andrew]

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?