Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Formula is =IF(ISERROR(DATEDIF(B5,B6,"d")),"waiting",REPT("I" ,C5))

    I want to conditional format the cells if there is an output either from REPT or ISERROR or cells in B that have at least 1 date. I tried "cell value is greater than 0", but it includes cells with no output, those with no dates in either DATEDIF cells[B10 & B11 for example]. How do I exclude these cells so only a cell that would output an error [or in my case, "waiting"] or a REPT output would be included?
    It seems if both DATEDIF cells are empty, there would be no output and therefore would not be greater than 0, but it does not work in this case. What does this output [both DATEDIF cells are empty] equal?

    If Formula is =IF(ISERROR(DATEDIF(B5,B6,"d")),"waiting",DATEDIF( B5,B6,"d")), then the conditional format "cell value is greater than 0" works. No formating is both DATEDIF cells are empty. Why the difference? I obviously must be missing something here.



    Thanks.

  2. #2
    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
    I don't completely understand your request: "I want to conditional format the cells if there is an output either from REPT or ISERROR or cells in B that have at least 1 date."
    What format do you want if it has the error (with waiting result), what for the REPTorder do you want. It will be either one or the other. When should it check for at least one date? If you check that first, the REPT option will never format...

    I don't understand: "How do I exclude these cells so only a cell that would output an error [or in my case, "waiting"] or a REPT output would be included?" Those are the only 2 options with the formula. It will always be one or the other, there is no 3rd option to exclude

    As to your question: "It seems if both DATEDIF cells are empty, there would be no output and therefore would not be greater than 0, but it does not work in this case. What does this output [both DATEDIF cells are empty] equal?" If both cells are empty, the datedif results in zero days (0) and is thus not an error and will be the REPT part of the equation. It will appear to be "blank" (a null string actually) if the value of C5 is zero. The length of the string is directly dependent on the value in C5.

    And finally: "If Formula is =IF(ISERROR(DATEDIF(B5,B6,"d")),"waiting",DATEDIF( B5,B6,"d")), then the conditional format "cell value is greater than 0" works. No formating is both DATEDIF cells are empty. Why the difference?" as mentioned, if both are blank then the value is zero and is thus not "greater than 0".

    Note: Blank cells will be treated as dates with a value of 0 and thus is equivalent to Jan 0, 1900 (=Dec 31, 1899) If both are blank the datediff result is zero (not an error). If B5 is blank it will datedif result will be the value in B6 (again, not an error). If B6 is blank you will get an error in datediff since the number of days is less than 0...

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Steve,
    Sorry for the confusion. Your explanation of the results of DATEDIF and REPT when the dates are blank was very helpful and explains why the same conditional format worked for the column where DATEDIF filled in, but would not work in the column where the REPT filled in.

    Back to my question, REPT returns 3 options/possibilities as I understand it.
    1-returns x number of "I"s
    2- returns "waiting"
    3- returns null string which shows as blank

    My goal is to have a conditional format be true in the REPT column when the first 2 options are returned, [ x number of "I" and "waiting"], but not the 3rd, [null string which shows as blank].
    How do I set up conditional format to include #1 & #2 only. I quess what I am asking, how do I exlude a null string return?
    Do I have to change the original formula to give me another return/output [other than null string] when dates are blank. Can it be done both ways?

    Hope this is clearer than original post.

  4. #4
    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
    Your option (3) is a subset of option (1) when x = 0, so I wouldn't state it is a fundamentally different....

    You can set a conditional format (a formula) that checks the value of C5. If =C5 = 0 (or check =Counta(B5:B6)=0), you have your option3. Note: since CF run in order so you want option 3 checked BEFORE option 1 since option 1 will be true if both are blank.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Steve,
    I am missing something because I cannot get it working. Attached is example. Please help me condition format it so in this case only D5 to D11 is formatted [or shaded] and D12 to D14 would not be shaded [as in column C], but be excluded.

    [attachment=91113:example.xls]
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Len - Will this do what you are looking for?

    Tim
    Attached Files Attached Files

  7. #7
    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
    It displays "days" for the same reason as has been pointed out: The VALUE in the cell is zero (0). When formatted as:
    ## "days"

    It will not display the 0 due to the format as ## (the # will not display insignificant zeroes), but the " days" is still shown. To not show the "days" when the value is equal to zero, you can use the custom format:
    ## "days";;

    Which will display nothing for a negative or a zero value.

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    To appear to be blank, could also add a second Conditional Format in column C. Make font white.

    see attached.
    Attached Files Attached Files

  9. #9
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Tim,
    Yes, the first conditional format worked as does =Bx>0.
    What I do not understand is why =Dx>0 [refer to cell itself] does not work in that it also shades empty cells. Apparently these empty cells do not = 0. D12 refers to or =(B13-B12). If both are empty does this not return a "0"? See Steve's first reply, "If both are blank the datediff result is zero ". And if it does, why does D12>0 format fail?

    Tim and Steve,
    My reference to ##"days" was not a query but only an explanation that the cells containing it were not cells with returns and should not be shaded, but thank you for the explanations anyway.
    I knew about the white font, but not the double semicolon.

  10. #10
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Len - Will this work for Column D formatting?

    [attachment=91122:Conditional Format.jpg]

    Use whatever colors you want for each condition.
    Attached Images Attached Images

  11. #11
    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
    Cells with text do not = 0, they are >0. Text is > numbers when used in comparisons, but has a value of 0, when used in a formula that converts the text to a umber (it converts when the formula needs a value)

    An EMPTY cell is equal to zero. Don't mistake a cell with value of a null string ("") which APPEARS empty to be empty. A null string is a text and is thus >0. Also the custom formatting trick I used only MASKS the value, the cell still has a value of 0 in those cases...

    The conditional format for D12 is based on B12 which is blank and is thus NOT a number, it does not use C12 which is the number...

    Steve

  12. #12
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Tim and Steve,
    Thanks for your patience, input and explanations. It is filling in gaps in my knowledge and will be helpful in the future.

    OK, I finally figured out where I was losing this. It was the REPT returning a string, not a number and that a blank string (or NULL string) is not "0".
    I wanted to use, as an alternative method for learning purposes, the REPT column exclusively without referring to the other columns for their numbers but to do that I needed to deal with strings, as in =LEN, not numbers. Thanks Tim for leading me there.

    Although =LEN works, as an alternative, is there a way to indicate or differentiate an empty string (NULL) and a not empty string (not NULL) directly, not indirectly through =LEN? Asked another way, in the example given and only using the REPT column without reference to other data, could we use something other than =LEN?

  13. #13
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Len - Does the function =ISBLANK() help?

  14. #14
    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
    You can compare to a null string

    =D12=""

    explicitly compares to the null string.

    You could also check the value of C12 since this is what REPT uses for determining the length of the string...

    Note: isblank checks for blanks and will always be false if the cell has a formula. A blank is an empty cell. A null string is a zero length string



    Steve

  15. #15
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by Len Smith View Post
    ........ is there a way to indicate or differentiate an empty string (NULL) and a not empty string (not NULL) directly, .....

    Quote Originally Posted by SteveA View Post
    ...Note: isblank checks for blanks and will always be false if the cell has a formula. A blank is an empty cell. A null string is a zero length string ....
    Try this in Cell E12, =ISBLANK(B12), then copy that formula to Columns F and G of Row 12.
    Result should be ....
    E12 - TRUE
    F12 - FALSE
    G12 - FALSE

    Len - Please consider posting a newer sample file of the project along with issues that need to be resolved.

Page 1 of 2 12 LastLast

Posting Permissions

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