SOLVED

# SharePoint Calculated Column Blanks & Negative numbers

Copper Contributor

# 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 get an error value when the Expiration Date is blank or if days until due is a negative number?

Thanks!

6 Replies
best response confirmed by analysta (Copper Contributor)
Solution

# Re: SharePoint Calculated Column Blanks & Negative numbers

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

# Re: SharePoint Calculated Column Blanks & Negative numbers

@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

# Re: SharePoint Calculated Column Blanks & Negative numbers

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

# Re: SharePoint Calculated Column Blanks & Negative numbers

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

# Re: SharePoint Calculated Column Blanks & Negative numbers

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

# Re: SharePoint Calculated Column Blanks & Negative numbers

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

1 best response

Accepted Solutions
best response confirmed by analysta (Copper Contributor)
Solution

# Re: SharePoint Calculated Column Blanks & Negative numbers

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