Create a Calculated Column

Raja 0 Reputation points
2023-05-30T16:53:14.0566667+00:00

I have a list wit Column named

a. Date Task Due ( Date Column)

b. Date Task Received ( Date Column )

c. Days until task complete : this will be calculated column based on following condition

  1. If Date Task Received is not blank than the value would be Task Received.
  2. If Date Task Received is blank and Date Task Due is past date , the value would be days between now and Date Task Due
  3. If Date Task Received is blank and Date Task Due is still within timeframe , the value would be blank.

I tried

=IF([Date Task Received]<>"","Task Received",

IF(Now()-[ Date Task Due] > 0, "Now()-[ Date Task Due]",""

IF(Now()-[ Date Task Due] < 0, "Now()-[ Date Task Due]","")))

but it is not working, can you please tell me what am i doing wrong ?

Thank you.

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,258 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
2,803 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Haoyan Xue_MSFT 22,231 Reputation points Microsoft Vendor
    2023-05-31T05:54:50.11+00:00

    Hi @Raja ,

    Use the following calculate formula:

    =IF(AND(ISBLANK([Date Task Received]),[Date Task Due]<NOW()),FIXED(NOW()-[Date Task Due]),(IF(AND(ISBLANK([Date Task Received]),[Date Task Due]>NOW()),"",TEXT([Date Task Received],"mm/dd/yyyy"))))
    

    The results of the run are shown in the following figure:

    User's image

    User's image


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Haoyan Xue_MSFT 22,231 Reputation points Microsoft Vendor
    2023-06-01T02:30:40.4166667+00:00

    Hi @Raja ,

    I understand your needs, I changed my formula:

    =IF(AND(ISBLANK([Date Task Received]),[Date Task Due]<NOW()),ROUNDUP((NOW()-[Date Task Due]),0),(IF(AND(ISBLANK([Date Task Received]),[Date Task Due]>NOW()),"","Task Received")))
    

    In addition, I have some doubts about getting the integer number of days. For example, if the number of days is calculated as 2.3 days, should it be directly displayed as 2 days or 3 days? In the formula I provided this time, it will be displayed as 3 days. If this doesn't meet your needs, you just need to replace ROUNDUP() with ROUND() or ROUNDDOWN() function.

    User's image


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments