Formula to Status Three Dates

[KATHWUN]

SAMPLE of Dates in database
A--------------------B--------------------C---------------------D
__________________________________________
---START-------END--------RESTART------------STATUS
1 02/02/02-------------------------------------------------Active
2 06/07/01----10/10/01---------------------------------Inactive
3 04/05/00----03/03/01----02/02/02----------------Active
__________________________________________

Need formula in STATUS column
(1)to interpret dates in relative row and
(2)to insert either "Active" or "Inactive" in STATUS cell.
Active = If START date is input
Inactive = if start date + END date are input
Active = if start date, end date, and RESTART date are input.

CONDITIONS:
All valid dates = earlier than the NOW date -- at least by =NOW() -1 day.
All valid dates = later than those in the preceding column.

Please notify if more info or an attachment is needed showing model.


[ This Message was edited by: KATHWUN on 2002-04-08 21:11 ]

[Dave Patton]

Edit or refine the following

=IF(OR(AND(A2>0,A2<$G$1,B2=""),AND(C2>0,B2<$G$1,C2<$G$1)),"Active","Inactive")

G1 has the following =today()



[ This Message was edited by: Dave Patton on 2002-04-08 21:05 ]

[Aladin Akyurek]

Assuming that A1:D4 houses the sample along with expected results:

In D1 enter and copy down:

=(COUNT(A2:C2)=3)*(C2
Select D2:D4 and custom format the selection as:

=0]"Inactive";[=1]"Active";General

Or, alternatively,

in D1 enter and copy down:

=IF((COUNT(A2:C2)=3)*(C2
Aladin

[KATHWUN]

Thank YOU, Dave Patton!
With slight variation (reversing Active/Inactive), your formula worked perfectly. I love this site!

[KATHWUN]

Thank you,too, Aladin.
I'll try your formula as well.
To think I almost lost hope -- and VOILA!
Genius at my fingertips.

[Aladin Akyurek]

On 2002-04-08 21:43, KATHWUN wrote:
Thank you,too, Aladin.
I'll try your formula as well.
To think I almost lost hope -- and VOILA!
Genius at my fingertips.

If you try, replace TODAY() by a cell ref as Dave (Dave Patton, that is) does.

[Dave Patton]

Please clarify what you mean by reversing.

The formula give the Active or inactive per your example.

[KATHWUN]

REPLY and QUESTION to Dave Patton:
I began at Row 4, put Today() at A1, above the title row, and this is the formula used:
=IF(OR(AND(A4>0,A4<$A$1,B4=""), AND(C4>0,B4<$A$1,C4<$A$1)),"Active","Inactive")

My earlier statement of transposing Active and Inactive was an error (mea culpa), Dave, which I noticed and corrected AFTER I sent the delighted reply. I didn't think you would react, so I didn't want to busy the board with the news of my discovery post-reply. The above, as you advised, worked perfectly.

QUESTION:
How do I construct a formula for inputting current-year dates, so that data entry only requires month/day (e.g., 1/31 or 3/5 or 4/15) and formula inserts /year (e.g., /02, and next year -- /03)?
Thanks in advance.
Kath

[ This Message was edited by: KATHWUN on 2002-04-09 19:56 ]

[Yogi Anand]

Hi KATHWUN:
Entering the date as 1/31, 3/5, or 4/15 in a cell will automatically take the date as for the current year.
so if you enter in a cell 1/31, and format the cell to show the date as 03/14/98, it will show up as
01/31/2002

HTH

[KATHWUN]

You're sooo right. It worked!
Thank you, Yogi.
Love the site!