I have a file with several worksheets, each of which contains a
different concert tour schedule. Each schedule is different from the
other in terms of number of show dates. Column A contains the
individual dates. Cell A1 contains the formula "=TODAY()", and cell A2
contains the very first date of each tour.

What I need to do is write a formula in cell B1 that compares cell A1
(today's date) with the first and last date of each schedule.

- If the first date (cell A2) is a future date, cell B1 should display
"Future".
- If the first date has passed but the last date is a future date, cell
B1 should display "Current".
- If the last date has passed, cell B1 should display "Completed".

What I don't know how to do (if this is even possible) is come up with
a formula that looks to the last cell of data in column A. I can "hard
code" it after I input the schedule, but since each schedule is
different (one tour may have 25 dates, another only 8), I would have to
change the formula each time.

Hopefully I am being clear -- can this be done?


--
LACA
------------------------------------------------------------------------
LACA's Profile: http://www.excelforum.com/member.php...o&userid=30381
View this thread: http://www.excelforum.com/showthread...hreadid=502190

B2:
=IF(A2A1,"Future",IF(INDEX(A:A,MATCH(9.9999999999 9999E+307,A:A))TODAY(),"C
urrent","Completed"))

--

HTH

RP

"LACA" wrote in message
...

I have a file with several worksheets, each of which contains a
different concert tour schedule. Each schedule is different from the
other in terms of number of show dates. Column A contains the
individual dates. Cell A1 contains the formula "=TODAY()", and cell A2
contains the very first date of each tour.

What I need to do is write a formula in cell B1 that compares cell A1
(today's date) with the first and last date of each schedule.

- If the first date (cell A2) is a future date, cell B1 should display
"Future".
- If the first date has passed but the last date is a future date, cell
B1 should display "Current".
- If the last date has passed, cell B1 should display "Completed".

What I don't know how to do (if this is even possible) is come up with
a formula that looks to the last cell of data in column A. I can "hard
code" it after I input the schedule, but since each schedule is
different (one tour may have 25 dates, another only 8), I would have to
change the formula each time.

Hopefully I am being clear -- can this be done?


--
LACA
------------------------------------------------------------------------
LACA's Profile:
http://www.excelforum.com/member.php...o&userid=30381
View this thread: http://www.excelforum.com/showthread...hreadid=502190