Create a Calculated Column

Question

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.

Answer 1

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:

---

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.

Answer 2

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.

---

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.