Forum Discussion

Alexandra420's avatar
Alexandra420
Copper Contributor
Jun 08, 2023
Solved

Help with SharePoint calculations

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 ge...
SharePoint Online
  • ganeshsanap's avatar
    ganeshsanap
    Jun 15, 2023

    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's avatar
ganeshsanap
MVP
Jun 09, 2023

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.

  • Alexandra420's avatar
    Alexandra420
    Copper Contributor
    Jun 15, 2023

    ganeshsanap 

    THANK YOU SO MUCH!  This worked like a charm.  🙂

    • ganeshsanap's avatar
      ganeshsanap
      MVP
      Jun 15, 2023

      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.

      • Alexandra420's avatar
        Alexandra420
        Copper Contributor
        Jul 05, 2023

        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:

        Any ideas?

        Thanks

        Alex

         

Resources