Ignoring Lines with Errors in Pivot Tables

D

Darren

Is there a way to ignore the lines that have errors, i.e. "#Value" and "####"
when I create my pivot tables?
 
D

Dave Peterson

Maybe you can use another column for your pivottable:

=if(iserror(b99),0,b99)

and use that in your PT.

What caused the ###'s?
 
D

Darren

The time went past midnight and so it is not being recognized. There are two
sets of time and dates with all of them in separate cells.
 
D

Dave Peterson

I'm not sure what you want to do when your time goes past midnight, but if it's
based on subtracting two times, you can do things like:

=(A1-B1)+(B1>A1)
which is shorthand for:
=(A1-B1)+IF(B1>A1,1,0)

If you really are subtracting times, it might be a good idea to include both the
time and date in those cells. Then you don't have to worry about going across 2
midnights.
 
Top