Forum Discussion
IF FALSE THEN BLANK
The problem is, When you create a pivot table, and want to use your date column of your source data as a Row value on the pivot table.
If your Date column has Dates and actual empty cells, the pivot table sees your date as a date, and you can group by things like Year, or Month or Quarter and expand and collapse on that, but if you have any "" values in your date column, the pivot table sees nothing as a date anymore, and all of a sudden, nothing is grouped, and everything is treated as text on the pivot table.
So far the only 2 Bad work-arounds seems to be:
- After your IF statement created "" as value, manually go and delete all cell values that have value "" so that the cell is actually empty.
- Write a macro to go delete all cell values if value is "" after changes happened.
Both really not ideal solutions and solution 2 is just an automation really of solution 1
GerrieGrotman wrote: ``The problem is, When you create a pivot table [....] the pivot table sees nothing as a date anymore, [...] and everything is treated as text on the pivot table.``
I'm afraid I know little-to-nothing about PTs. I suggest that you post a new question and make the subject specifically about null strings in PT data.
Off-hand, I wonder if the following would solve the problem....
Instead of using IF( ... , ""), how about using IF( ... , 0) and the following Custom cell format to make the cell appear to be blank: [=0]"";m/d/yyyy .
(And is there a way to coerce the PT to ignore zero-valued cells?)
Again, if that is an uneducated misdirection, let's not discuss it here (except to acknowledge that it does not work, for the benefit of other readers).
If you create a new discussion with an appropriate title, you are likely to attract helpers who are knowledgeable about PTs.