1

I am trying to figure out the correct formula for my SharePoint calculated column. I have two date columns [Date in Queue] and [Date Completed]. I need to calculate number of days between the two dates and if there is no date completed then I need it to calculate number of days from date in queue to today.

The issue is I have blanks in both columns; my current formula produces some random 45,189 number when I have blanks for that first date.

How can I modify my formula to produce blank fields when there is blanks in both [Date in Queue] and [Date Completed], take the difference of the two dates and take the difference between [Date in Queue] to today when [Date Completed is blank].

Current Formula:

=IF(AND(ISBLANK([Date Completed])),DATEDIF([Date in Queue],TODAY(),"d"),IF(OR(NOT(ISBLANK([Date in Queue])),NOT(ISBLANK([Date Completed]))),DATEDIF([Date in Queue],[Date Completed],"d"),""))

1 Answer 1

0

Try using formula like below for your SharePoint calculated column:

=IF(ISBLANK([Date Completed]),IF(ISBLANK([Date in Queue]),"",DATEDIF([Date in Queue],TODAY(),"d")),IF(ISBLANK([Date in Queue]),"",DATEDIF([Date in Queue],[Date Completed],"d")))

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. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.