I'm trying to display KPIs that show how long a list item spend in each status.

Column name = Time In 5.

=IF(OR(ISBLANK([Date entered 6. ]),ISBLANK([Date Entered 5. ])),"No KPI dates",DATEDIF([Date entered 6. ],[Date Entered 5.],"D"))

Some items display a number (expected), some display "No KPI Dates (expected) but some are displaying "#Name?". What would make this appear?

  • Does this Date entered 6. and Date Entered 5. are date fields? Also how you have returned your calculated field as number or something else? Nov 27, 2018 at 16:14
  • Also there are spaces after your column names in formula?? Nov 27, 2018 at 16:39
  • @GaneshSanap yes they are date fields. Calculated field is single line of text so that "No KPI Dates" could be displayed. No there are no spaces, I just removed the full name of that field
    – Enilorac
    Nov 28, 2018 at 8:59

2 Answers 2


Following formula returns the number of days between the two dates:

DATEDIF(Date1, Date2, "d")

Which is equivalent to Date2 - Date1 and return the days only.

In your case, you are subtracting "Date entered 6." from "Date Entered 5.".

So for the items which has [Date entered 6.] > [Date Entered 5.], calculated column returning as #Name?.


  1. Use Validations on dates so that Date Entered 5. will always be greater than Date entered 6..

  2. Use ISERROR() function:

ISERROR() takes only one parameter and returns TRUE or FALSE. It is typically used inside an IF() statement to evaluate a formula. What you probably want is something like

=if(ISERROR(DATEDIF([DATE1] , [DATE2] ,"d")),"0", DATEDIF([DATE1] , [DATE2] ,"d"))

In words: If the DATEDIF function returns an error, use "0", else use the result of the DATEDIF function.

For Your Case, Following formula will work:

=IF(OR(ISBLANK([Date entered 6.]),ISBLANK([Date Entered 5.])),"No KPI dates",IF(ISERROR(DATEDIF([Date Entered 5.],[Date entered 6.],"D")),"Date entered 6 is less than Date Entered 5",DATEDIF([Date Entered 5.],[Date entered 6.],"D")))
  • 1. Date entered 6 will always be greater or equal to Date entered 5
    – Enilorac
    Nov 28, 2018 at 9:08
  • 2. Is this instead or as well as my existing formula? I'd like to keep displaying "No KPI dates" for those in an earlier status
    – Enilorac
    Nov 28, 2018 at 9:10
  • So, you need to change DATEDIF([Date entered 6. ],[Date Entered 5.],"D") to DATEDIF([Date Entered 5.],[Date entered 6. ],"D"). For Complete formula, check my updated answer. Nov 28, 2018 at 9:11
  • Isn't that the same as what I already have?
    – Enilorac
    Nov 28, 2018 at 9:15
  • No, look closely. it is different than what you have written. I have changed the places of Date entered 6 and Date Entered 5. Nov 28, 2018 at 9:19

This should work for you:

=IF(OR(ISBLANK([Date entered 6. ]),ISBLANK([Date Entered 5. ])),"No KPI dates",IF(ISERROR(DATEDIF([Date entered 6. ],[Date Entered 5.],"D")),"0",DATEDIF([Date entered 6. ],[Date Entered 5.],"D"))
  • This has got rid of the #Name? but now some of them are just blank, not 0, nothing at all
    – Enilorac
    Nov 28, 2018 at 9:07
  • Its because we left it blank. There is blank quotes before last DATEDIF (""). Enter the value there which you want to show instead of blank. (Updated the answer and added "0") Nov 28, 2018 at 9:16

Your Answer

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

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