Difference in Dates, with Default Condition

[bdav1216]

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?

[vlady]

try

Please Login or Register  to view this content.

[bdav1216]

try

Please Login or Register  to view this content.

Thank you. How would this be modified to account for blank dates in Column H or AN? When either are blank (or the condition you added), the value of the formula, should be -1.

[Phuocam]

Trt this ...

=IF(COUNT(H2,AN2)<2,-1,MAX(-1,H2-AN2))

[bdav1216]

Thank you for the assistance.

One other variation.

Column C = Total Time
Column A = Time1
Column B = Time2

(Time1 +Time2 should equal Total Time), but there will be instances where the time's are illogical.

I need help calculating Column A. It should be calculated from the difference of C - B, but I need to deal with specific illogical conditions.

1) If Column C = blanks " ", how do I set this to a value of '-1'.
2) If Column B or C = '-1', then the value of Column A should be set to a value of '-1'.
3) If the difference of C-B results in a negative value for Column A, the value in Column A should be set to a value of '-1'.

I attached a sample workbook for reference.

[JeteMc]

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:

Please Login or Register  to view this content.

Let us know if you have any questions.

[bdav1216]

Thanks for the formula, this looks to be what I need.

One quick question. If Column C is blank filled, how do I set the value to -1?

[JeteMc]

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:

Please Login or Register  to view this content.

Let us know if you have any questions.