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?

  • 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 Nov 27 '18 at 16:14
  • Also there are spaces after your column names in formula?? – Ganesh Sanap Nov 27 '18 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 '18 at 8:59
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")))
  • 1. Date entered 6 will always be greater or equal to Date entered 5 – Enilorac Nov 28 '18 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 '18 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 Nov 28 '18 at 9:11
  • Isn't that the same as what I already have? – Enilorac Nov 28 '18 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 Nov 28 '18 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"))
  • This has got rid of the #Name? but now some of them are just blank, not 0, nothing at all – Enilorac Nov 28 '18 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 Nov 28 '18 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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