Negative return in =DATEDIF function

[Steve]

Is it possible to display the result of a negative DATEDIF function? I
am checking a column of warranty expiration dates against the current
date, and displaying that in an adjacent column. When the item is past
its expiration it shows the #NUM! error. How would I format this to
show the number of days past its expiration?

 

[Dave O]

Instead of using a formula that returns an error when numbers are
negative you might use a simple subtraction instead: for instance if
the expiration is 1/15/2006 in cell A1 and today's date is 3/2/2006,
you might use the formula
=TODAY()-A1

This returns a positive 46, though, and it sounds like you want to show
a negative number when the expiration date is in the past, so try
=0-(TODAY()-A1)

If your expiration date is in the future, that formula shows a positive
number. Does that resolve your question?

 

[Guest]

Why not just subtract the expiry dates from today's date? With the date in
D2, use

=today()-D2

Format the cell as general

 

[Mark Lincoln]

All you really need to do is subtract the current date from the
warranty expiration date. If the result is less than zero, the
warranty has expired.

If you want to show the number of days past expiration, you could use
something like this, using B8 for an example:

=IF(B8<TODAY(),ABS(B8-TODAY()),"")

Format the cell as General.

This shows blank if the warranty has not expired, and the number of
days past warranty once expired.

 

[Steve]

That does work. I formatted the cells to make negative numbers red, so
it is more clear at a glance.

Thanks fot the help!