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

    Formula explaination and help (2003)

    Greetings,

    This is a continuation of a prior post, in a different view.

    For the attached, I handled the blank vs. cell having a space. I was able to clear the resultant information because the data was off base for calculations. Due to the fact the cell D3 and E3 are empty, the calc puts the information for the todays date. What I need is to handle all of the resultant data.

    In the cell for column A4 I put the following:

    =IF(D4="",IF(E4="","",(TODAY()-E4,D4-E4+1)))

    Now I am not getting the results I expected. What (in english) is the formula really saying? I expected it to say if d4 and e4 are blank, then display the cell as blank, else calc the diff between today and e4, or calc the diff beweeen d4 and e4 +1

    The First calc should be based upon only d4 being blank. The second calc is for when both d4 and e4 have dates.

    Regards
    Brad
    Attached Files Attached Files

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

    Re: Formula explaination and help (2003)

    You'll also have to build in a check in the formula in B4, to return "" if A4 is blank.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula explaination and help (2003)

    How about thhis:

    =IF(D4="",IF(E4="","",TODAY()-E4),D4-E4+1)

    Steve

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

    Re: Formula explaination and help (2003)

    Steve,
    I think this is doing what I want, but what does the formula actually say?
    =IF(D4="",IF(E4="","",TODAY()-E4),D4-E4+1)



    Secondly, I tried the same thought line with this forumula but I must be messing up.

    This is the formula as is from the file I attached in the original post to this thread. I would like to do the same type of check if BOTH E and D are blank.


    =IF(NETWORKDAYS(E2,D2,Calendar!$E$1:$E$25)<0,TODAY ()-E2,IF(D2>0,NETWORKDAYS(E2,D2,Calendar!$E$1:$E$25), IF(D2<0,NETWORKDAYS(E2,D2,Calendar!$E$1:$E$25))))


    Thanks,
    Brad

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula explaination and help (2003)

    The formula:
    =IF(D4="",IF(E4="","",TODAY()-E4),D4-E4+1)

    Has the logic:
    If D4 is null then check if E4 is null.
    If (in addition to D4 being null), E4 is also null then the result is null string
    If (in addition to D4 being null), E4 is not a null, the result is TODAY()-E4
    If D4 is not null then the result is D4-E4+1

    [By implication if D4 is NOT null, but E4 is null the result will still be D4-E4+1, but since a null E4= 0, the result is D4+1]

    Are you looking for something like this:

    =IF(D2="",IF(E2="","",TODAY()-E2),NETWORKDAYS(E2,D2,Calendar!$E$1:$E$25))

    If not, what do you want in all 4 cases:
    1)D2=E2 = null
    2)D2 = null, E2 is not null
    3)D2 is not null, e2 = null
    4)D2 is not null, e2 is not null

    The above yields in each case:
    1)null
    2)TODAY()-E2
    3)NETWORKDAYS(0,D2,Calendar!$E$1:$E$25)
    4)NETWORKDAYS(E2,D2,Calendar!$E$1:$E$25)

    Steve

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

    Re: Formula explaination and help (2003)

    Steve, Thanks for the explaination...I shall have to digest this for a while.

    Thanks again,
    BRad

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

    Re: Formula explaination and help (2003)

    Steve,


    I think I get it.!!!

    I worked the example formula which uses the networkdays and it produced what I needed.



    Thanks for the explaination and help,
    Brad

Posting Permissions

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