Working with dates

  • Thread starter Thread starter andrew
  • Start date Start date
A

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?
 
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
 
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
 
Almost there. What if i want just the number in BOLD while the rest of the
text remains in regular format?
 
Back
Top