Formula to Status Three Dates

KATHWUN

Board Regular
Joined
Apr 7, 2002
Messages
61
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
Assuming that A1:D4 houses the sample along with expected results:

In D1 enter and copy down:

=(COUNT(A2:C2)=3)*(C2<TODAY())*(B2<C2)*(A2<B2)+(COUNT(A2:C2)=2)*(B2<TODAY())*(A2<B2)*0+(COUNT(A2:C2)=1)*(A2<TODAY())

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<TODAY())*(B2<C2)*(A2<B2),"Active",IF((COUNT(A2:C2)=2)*(B2<TODAY())*(A2<B2),"Inactive",IF((COUNT(A2:C2)=1)*(A2<TODAY()),"Active","")))

Aladin
 
Upvote 0
Thank YOU, Dave Patton!
With slight variation (reversing Active/Inactive), your formula worked perfectly. I love this site!
 
Upvote 0
Thank you,too, Aladin.
I'll try your formula as well.
To think I almost lost hope -- and VOILA!
Genius at my fingertips.
 
Upvote 0
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.
 
Upvote 0
Please clarify what you mean by reversing.

The formula give the Active or inactive per your example.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top