2

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?

Improve this question
3
  • 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?
    – Ganesh Sanap - MVP
    Commented Nov 27, 2018 at 16:14
  • Also there are spaces after your column names in formula??
    – Ganesh Sanap - MVP
    Commented 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
    Commented Nov 28, 2018 at 8:59

2 Answers 2

Reset to default
0

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?.

Workaround:

  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")))
Improve this answer
5
  • 1. Date entered 6 will always be greater or equal to Date entered 5
    – Enilorac
    Commented 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
    Commented 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.
    – Ganesh Sanap - MVP
    Commented Nov 28, 2018 at 9:11
  • Isn't that the same as what I already have?
    – Enilorac
    Commented 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.
    – Ganesh Sanap - MVP
    Commented Nov 28, 2018 at 9:19
0

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"))
Improve this answer
2
  • This has got rid of the #Name? but now some of them are just blank, not 0, nothing at all
    – Enilorac
    Commented 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")
    – harshal gite
    Commented Nov 28, 2018 at 9:16

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.