Difference in Dates, with Default Condition

H2 is a date field (ex. 01/13/2017)

AN2 is a date field (ex. 01/12/2017)

Here is my formula:

=IF(H2="","-1",IF(AN2="","-1",DATEDIF(AN2,H2,"D")))

In the example above, my forumula is returning a value of '1' but my formula below is returning a value of #NUM! in situations where H2 is an older date than AN.

Ex.

H27 = 12/08/2016 and AN27 = 01/11/2017 returns a #NUM!

H28 = 01/03/2017 and AN28 = 01/06/2017 returns a #NUM!

I would like to fix this by modifying my formula to say if the difference in dates is either GE or LE 99, set the value to -1.

How would I modify my existing formula to accomplish that?

try

Formula:

=IF(H2>=AN2,DATEDIF(AN2,H2,"d"),-1)

If I understand correctly any of the three conditions, Bn=-1, Cn="" or Bn>Cn, should cause An to display -1. Otherwise An should display the difference in Cn and Bn. If that is correct then try the following formula in A2 and down:

Formula:

=IF(OR(B2=-1,C2="",B2>C2),-1,C2-B2)

Let us know if you have any questions.

If you are asking how to set the value of C2 to -1 when C2 starts as a blank cell, then I believe that you will need to seek advice from a member that is proficient in VBA, as I don't know of a way to do that by formula. It also appears that I need to make a correction in the formula based on this new understanding. Since the formula that populates column A isn't concerned with whether or not the corresponding value in column C is blank, rather whether or not it contains a value of -1, the formula should read:

Formula:

=IF(OR(B2=-1,C2=-1,B2>C2),-1,C2-B2)

Let us know if you have any questions.