DATEDIF function to also work with negative numbers?

Question

I am trying to write a very simple equation as Calculated Column on SharePoint List. This equation works easily in Excel, but I'm having trouble with it on SharePoint List.

I am trying to find the difference of days from [RDC Deadline] to TODAY(), whether it's positive or a negative number (a negative number would mean that it's that many days overdue). In excel, I have a simple DATEDIF function and it provides both positive and negative numbers with no problem, but on SharePoint if the number is negative then there will be a #NUM! error. Is there any way I can go around this and get both negative and positive numbers? I tried an IF statement too but it was unsuccessful.

Thank you!

Answer 1

In excel, I have a simple DATEDIF function and it provides both positive and negative numbers with no problem

Are you sure about this? It does not in my Excel.

In any case, just subtract the two dates. Dates are stored as numbers with 1 = 1/1/1900, 2 = 1/2/1900, etc.

=[RDC Deadline] - TODAY()

Keep in mind that TODAY() is not recalculated each time the list is viewed. It is only recalculated when the list item is edited. (Create a test list and add a calculated column with ="NOW()", add a few items to the list, note the times, and then refresh the page and note that the times do not change. Edit one of the items and note that only that one has its time updated.)