SOLVED

Help with SharePoint calculations

Copper Contributor

Hi,

 

I am using the below formula to calculate how many days a task is overdue, but do not want to show #NUM! when the due date has not been passed, and/or the due date is missing.  With this I get a syntax error, can anyone help me?  What am I doing wrong?  Thank you so much.

 

=IF(OR(ISBLANK(“Due date”),"0",IF(“Due date” > today(),-DATEDIF(today(),”Due date”,"d"),DATEDIF(“Due date”,today(),"d")))

 

 

4 Replies

@Alexandra420 Use formula like below: 

 

=IF(OR(ISBLANK([Due date]),[Due date]=""),"0",IF([Due date] > TODAY(), -DATEDIF(TODAY(),[Due date],"d"),DATEDIF([Due date],TODAY(),"d")))

 

You have to wrap column inside square brackets like [Due date]

 

Note

  1. Sometimes comma( , ) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon( ; ) instead of comma( ).
  2. Use correct display name of your SharePoint columns in above formula.
  3. Column names can be case sensitive. So, use column names in formula accordingly.

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.

@ganeshsanap 

THANK YOU SO MUCH!  This worked like a charm.  :)

best response confirmed by Alexandra420 (Copper Contributor)
Solution

@Alexandra420,

 

You are welcome. Glad to hear that it worked for you!


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.

@ganeshsanap 

 

Good morning ganeshsanap,

Now the formula doesn't calculate correctly. I used what you gave me:

=IF(OR(ISBLANK([Due date]),[Due date]=""),"0",IF([Due date]>TODAY(),-DATEDIF(TODAY(),[Due date],"d"),DATEDIF([Due date],TODAY(),"d")))

and the #of Days Overdue is not calculating the correct overdue days - see below screenshot:

Alexandra420_0-1688565475382.png

Any ideas?

Thanks

Alex

 

1 best response

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

@Alexandra420,

 

You are welcome. Glad to hear that it worked for you!


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.

View solution in original post