SharePoint Calculated Column Blanks & Negative numbers

[analysta]

Hello - I have a calculated column that calculates the amount of days until the due date. My formula is:

=DATEDIF(TODAY(),[Contract Expiration Date],"D")

How can I revise this formula so I don't get an error value when the Expiration Date is blank or if days until due is a negative number?

Thanks!

[#2 · Accepted solution]

I figured out the correct formula. Here it is in case anyone else needs it.

=IF(ISBLANK([Contract Expiration Date]),"",IF(ISERROR(DATEDIF(TODAY(),[Contract Expiration Date],"d")),-DATEDIF([Contract Expiration Date],TODAY(),"d"),DATEDIF(TODAY(),[Contract Expiration Date],"d")))

[#3]

@analysta Or something like this might also work for you: 

=IF(ISBLANK([Contract Expiration Date]),"",IF(TODAY()>=[Contract Expiration Date],DATEDIF([Contract Expiration Date],TODAY(),"d"),DATEDIF(TODAY(),[Contract Expiration Date],"d")))

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

[#4]

@analysta Hi I found your response which works well to calculate days between today and a Due date. Do you know if/how I can swap out TODAY for a date in another column? i.e. I went to know the days betweem dates in another column and the due date. I want negative numbers to show also so that if the date is later than the due date, I can see how many dates it is late by.

[#5]

@ej24-9 Try using formula like: 

=IF(ISBLANK([Due Date]),"",DATEDIF([Another Date Column],[Due Date],"d"))

Make sure to use the correct display names of your date columns in the SharePoint list.

---

Please consider giving Like if my post helped you in any way.

[#6]

Thank you! This has helped but I am still getting #NUM! for dates beyond the Due Date i.e. negative results

[#7]

@ej24-9 Try using this: 

=IF(ISBLANK([Due Date]),"",IF([Another Date Column]>[Due Date], -1 * DATEDIF([Due Date],[Another Date Column],"d"),DATEDIF([Another Date Column],[Due Date],"d")))

---

Please consider giving a Like if my post helped you in any way.