Thanks:  0
Likes:  0

# Thread: Formula to Status Three Dates

1. 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.

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

2. 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 ]

3. 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

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

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

6. On 2002-04-08 21:43, KATHWUN wrote:
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.

7. Please clarify what you mean by reversing.

The formula give the Active or inactive per your example.

8. 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)?
Kath

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

9. 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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•