# Calculated column displaying "#Name?"

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

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 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 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. Commented Nov 28, 2018 at 9:11
• Isn't that the same as what I already have? 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. Commented 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 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") Commented Nov 28, 2018 at 9:16