1

I'm going crazy here trying to sort the below out.

I am trying to hide the "0" (J9) and the "Yes" (K9) until the dates have been filled in

The Formula in J9 is =DATEDIF(H13,I13,"d")
The Formula in K9 is =IF(J13<=5,"Yes","No")

      H9               I9                 J9                   K9 

Date meter fitted   Date details rec'd  Time to Receive Within 5 days ?
                                           0                   Yes
                                           0                   Yes
                                           0                   Yes
01/03/2014          01/05/2014             61                  No
                                           0                   Yes
                                           0                   Yes

If any one can help it would be great!!!

Improve this question
1
  • Dan, when you write a question, there are options to apply formatting. Although it's not obvious, you can use the code option which will render your content more verbatim (although it's not perfect). I've done it for this time
    – Dave
    Mar 7, 2014 at 11:15

3 Answers 3

Reset to default
2

replace K9 with:

=IF(AND(H13="",I13=""),"",IF(J13<=5,"Yes","No"))

So as long as H13 and I13 are empty, nothing will be displayed, otherwise the result of your formula will. Apply the same principle to the other one.

replace J9 with:

=IF(AND(H13="",I13=""),"",DATEDIF(H13,I13,"d"))

Depending on your particular situation you might have to use OR instead of AND. Maybe this becomes more understandable, if i write it more like regular program code:

IF (H13="" AND I13="")
THEN K9=""
ELSE K9="the result from your formula"
Improve this answer
2
  • That works perfect for K9, Thank you!!but i cant get it to work for J9....
    – Dan G
    Mar 7, 2014 at 10:41
  • You can basically find&replace your formulas, the part for hiding the result while H13 and I13 are empty remains identical.
    – TheUser1024
    Mar 7, 2014 at 12:47
1

you can use conditional formatting to make the text white if the input is missing. this has the advantages that it doesn't complicate the formula itself; calculation of the result and whether to show the result are separated

Improve this answer
1

To find the difference in days between two dates a subtraction is sufficient, e.g.

=I13-H13

To calculate that only when two dates are present you can use COUNT, i.e.

=IF(COUNT(H13,I13)=2,I13-H13,"")

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .