Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Additional Condition in equation (2003)

    Greetings,

    I have the following formula, and need to have 1 more condition. Have tried a couple of ways, but still cannot get it to work.
    The formula as listed works as it is intended.

    Need to add a condition where IF DH2 is blank, then today - Y2

    I just could not figure the formula. This I believe can be the first check in the formula, because if DH2 is blank, the rest would not not be an issue.

    AX = Lifecycle state (text field)
    DF = Disposition (text field)
    DH = Disposition Date
    HY = Date Submitted

    Y - Creation Date

    =IF(OR(AX2="Incorporated",DF2="Change Cancelled",DF2="Change Rejected"),DH2-HY2+1,IF(OR(AX2="Authoring",AX2="Executing",AX2="R eady"),TODAY()-DH2+1,IF(DH2="",IF(HY2="","",TODAY()-HY2),DH2-HY2+1)))


    Thanks,
    Brad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Additional Condition in equation (2003)

    You already have a condition with DH2="" in the formula:

    IF(DH2="",IF(HY2="","",TODAY()-HY2)

    That seems to contradict your wish to have today - Y2.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Additional Condition in equation (2003)

    Ok... let me digest this....

    Some how I need to ensure I get a calculated number of days if the change has not been submitted. Field Y is the Create date.



    Edited: Maybe I check for NO DISPOSITION in the DF field, then do the calc?


    any thoughts?

    Brad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Additional Condition in equation (2003)

    I don't know what the purpose of your formula is, and you haven't actually told us what problem you have expanding it, so...

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Additional Condition in equation (2003)

    IF(OR(AX2="Incorporated",DF2="Change Cancelled",DF2="Change Rejected"),DH2-HY2+1,IF(OR(AX2="Authoring",AX2="Executing",AX2="R eady"),TODAY()-DH2+1,IF(DH2="",IF(HY2="","",TODAY()-HY2),DH2-HY2+1)))


    Basically, I am Calculating the number of DAYs a item is/has been open.
    First it looks a the Lifecycle state (Cell AX2) If it contains Incorporated, or if DF2 contains Change
    Cancelled or Change rejected) then calc the diff between Dispositon Date (DH2 and Date Submitted HY2) +1
    then IF AX2 is Authoring, Executing or Ready) then calc today - Disposition Date (DH2). If for some
    reason disposition date (DH2) is blank, and if date Submitted (HY2) is blank then calc today - date submitted(HY2) IF Disposition date is not blank then calc disposition date - Submitted date (HY2).

    I think I would like it to calc the number of days open IF it has not been submitted. If not submitted
    then the date submitted would be blank, and there will not be a disposition date.

    I think that would get all bases covered.

    Creation Date cell is (y2)

    First, am I reading the entire fourmla correct from a syntax point? Maybe the calc
    IF(DH2="",IF(HY2="","",TODAY()-HY2) is incorrect. maybe it should say Today()-Y2)...



    will try and let you know...


    EDITED: I changed the formula as such: Removed the AX2="Authoring" from the formula, and changed
    the last section IF(HY2="","",TODAY()-HY2), to IF(HY2="","",TODAY()-Y2),
    Now I get the blank field. However I need to have a calculated days if everything falls through the preceding calcs.



    Brad

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Additional Condition in equation (2003)

    Ok,,, Let's disregard the previous stuff in this thread.

    Attached is a spreadsheet with examples of the various conditions. I put what I thought the formula is doing logic wise (please correct as necessary for future understanding).

    I also put the 2 conditions I need to cover within the formula.

    Lets go from here....

    Thanks,
    Brad
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Additional Condition in equation (2003)

    Does this do what you want?

    =IF(AND(DH2="",AX2="Authoring",DF2="No Disposition"),IF(HY2="",TODAY()-Y2,TODAY()-HY2),IF(OR(AX2="Incorporated",DF2="Change Cancelled",DF2="Change Rejected"),DH2-HY2+1,IF(OR(AX2="Authoring",AX2="Executing",AX2="R eady"),TODAY()-DH2+1,IF(DH2="",IF(HY2="","",TODAY()-HY2),DH2-HY2+1))))

  8. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Additional Condition in equation (2003)

    Hans,

    Absolutly,!!!

    Can you breakdown this formula Logically? I really am not clear as to what is "does" from point to point.


    Thanks again,


    Brad

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Additional Condition in equation (2003)

    =IF(AND(DH2="",AX2="Authoring",DF2="No Disposition"),IF(HY2="",TODAY()-Y2,TODAY()-HY2),IF(OR(AX2="Incorporated",DF2="Change Cancelled",DF2="Change Rejected"),DH2-HY2+1,IF(OR(AX2="Authoring",AX2="Executing",AX2="R eady"),TODAY()-DH2+1,IF(DH2="",IF(HY2="","",TODAY()-HY2),DH2-HY2+1))))

    It first checks whether DH is blank, AX contains "Authoring", and DF contains "No Disposition".
    If so, it looks at HY. If this is blank, compute Today - Y, else compute Today - HY.
    Otherwise, use your original formula.

Posting Permissions

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