Help on Sharepoint Formula

Question

I wanted to have a Sharepoint formula to count days; I have Date Created, Date Completed, I wanted to count the days from when it created to today if not yet closed, and if when a task has been completed

Answer 1

Dear User,

Good day!!

Apologies for the inconvenience caused and please don’t worry, let’s work together on your concern and move towards a resolution path.

As per the description shared, I understand you are looking for the formula to get the number of days based on the condition and generally you can achieve your requirement with the IF formula in the Calculated Column of SharePoint Online list. However, they are few things that need to be confirmed which will help us to give clear idea on your required output:

1. Can I know the condition for getting the count of Date Started and TODAY? Is it when the task is not yet completed?
2. If the task is completed, you want to count the days between Date Started and Date Completed?
3. What’s the name of the Column that indicated whether the task is completed or not?

Note: If possible, please make a sample date with the required output (you can use Excel for reference data), where it will help us to better understand your requirement and provide you correct formula.

For example, if the name of column to show whether the task is completed or not is Status, then formula will be:

=IF([Status]=”Completed”, ([Date Completed – Date Started]), IF([Status]=”Not Closed”, DATEDIF([Date Started],TODAY(),”D”),””))

Note: The above formula is just an example.

Appreciate your patience and understanding. Have a great day!!

Best Regards,

Sophia

Answer 2

Hi Sophia,

The formula works THANK YOU!!! although I was wondering if we can have the day counted as one if the start date and completed date is the same day, currently it is showing as 0. is that possible? Also, this counts calendar days is that correct, can we exclude weekends as well in case?

Answer 3

Hi Sophia,

Thanks for your help. Super appreciate it! I do have a status column as shown below.

.

I tried this formula (got it by looking at online - =IF(AND(NOT(ISBLANK([Date Started])),ISBLANK([Date Completed])),DATEDIF([Date Started],TODAY(),"d")," ") -> but some items are showing as blank even if there is a date, is there something wrong with this formula?

Also, can we return the value to zero if both the start date and end date are blank?

Answer 4

Dear User,

Thanks for writing back and updating with us.

As per the formula provided, the output values provided in the column TAT are correct and the output values in the second screenshot are blank because the one of the condition provided in AND is not correct.

In brief, the formula you have provided: =IF(AND(NOT(ISBLANK([Date Started])),ISBLANK([Date Completed])),DATEDIF([Date Started],TODAY(),"d")," ")

In the above formula, if the both conditions in AND function is true, then it gives the value based on the formula provided in the value if true section: DATEDIF([Date Started],TODAY(),"d")

However, if one of the conditions is false is AND function, then the value will be blank because value if false is blank.

For example:

Date Started Date Completed

1/4/2024 1/4/2024

In the above used formula one of the condition in the AND function is ISBLANK([Date Completed]) but in the above example Date Completed is having the value which caused logic to false. So, the value of false is blank in the IF condition and that’s the reason we got blank as output.

Even though it’s not clear on the output you are looking, I assume you are looking for the following output:

If yes, please try the below formula:

=IF(AND(NOT(ISBLANK([Date Started])),ISBLANK([Date Completed])),DATEDIF([Date Started],TODAY(),"d"),IF(AND(NOT(ISBLANK([Date Started])),NOT(ISBLANK([Date Completed]))),(-DATEDIF([Date Started],[Date Completed],"d")),0))

If not, please help us with the more details about the conditions and output required, where we will try our best and assist you with required formula.

Appreciate your patience and understanding with us. Have a great day!!

Best Regards,

Sophia

Answer 5

Dear User,

Thanks for writing back and updating with us.

As per the further updates, if you want to show the value as 1 when Date Started and Date Completed as one, please use the below formula:

=IF(AND(NOT(ISBLANK([Date Started])),ISBLANK([Date Completed])),DATEDIF([Date Started],TODAY(),"d"),IF(AND(ISBLANK([Date Started]),ISBLANK([Date Completed])),0,IF([Date Started]=[Date Completed],1,IF(AND(NOT(ISBLANK([Date Started])),NOT(ISBLANK([Date Completed]))),(-DATEDIF([Date Started],[Date Completed],"d")),0))))

Moreover, if you want to exclude the weekends, then the formula to be used will be complicated and instead of depending on SharePoint Calculated column, I would like to recommend you with Power Apps and I suggest you to post your concern in the Power Apps Community - Power Platform Community (microsoft.com) to get the detailed help from the experts.

Thanks for understanding and if you feel my reply helped to explain your situation, please provide your feedback via the options below.

In the future, if you have any further concerns related to Office 365, please feel free to post a new thread in this community and we will try our best to assist you.

Have a good day!!

Best Regards,

Sophia