Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Formula to Status Three Dates

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    California
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,484
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default


    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. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    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


  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    California
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    California
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,484
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default


    Please clarify what you mean by reversing.

    The formula give the Active or inactive per your example.

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    California
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    California
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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