Forum Discussion

Copper Contributor

Feb 23, 2023

SharePoint Calculated Column Blanks & Negative numbers

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 ge...

SharePoint Online

analysta
Mar 07, 2023
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")))

Copper Contributor

Mar 07, 2023

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")))

ej24-9
Copper Contributor
Aug 15, 2023
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.
- ganeshsanap
  MVP
  Aug 15, 2023
  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.
  - ej24-9
    Copper Contributor
    Aug 15, 2023
    Thank you! This has helped but I am still getting #NUM! for dates beyond the Due Date i.e. negative results
ganeshsanap
MVP
Mar 08, 2023
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

Share

Resources