# Null Date Fields

#### cgrablew

##### New Member
I ran into a problem with pivot table not recognizing date fields when they are null. I created a date column in a table using a formula that assigns a null if the condition is FALSE. What I discovered that assigning a """" (null) to a cell is not they same as a cell where I delete the value. The formula created null cell is a Type 2 and cell where the null value is deleted is a Type 1 (Type function). If I do a compare of the 2 cells, they are equal (=cell1 = cell2 returns a true). The cells where the null cells were deleted are treated as dates perfectly in a pivot table. The others null cells created by the formula are not recognized as dates in the pivot table. You can also try this...create one null cell with a formula and the other by deleting the contents of a cell cell. Then do a month(...) function of the 2 cells ... you get a value error in null cell created by the formula and a 1 in the other with the contents deleted. WHY? How do I use the table data with the null formula in a pivot tables (I need the Month Rollup and I can't group the null formula date cells)?

PS: Hopefully this makes sense ... See example below

 A1 B1 C1 D1 E1 F1 G1 H1 FormulaText NOT FORMULA =IF(C3=0,"") NOT FORMULA =D3=E3 =MONTH(D3) =MONTH(E3) Value/Formual 0​ TRUE​ #VALUE!​ 1​

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### sandy666

##### Banned - Rules violations
"" is an empty string (ISBLANK() = FALSE) (ISTEXT() = TRUE)
nothing in cell is a null (ISBLANK() = TRUE) (ISTEXT() = FALSE)
"" and null are not the same

Last edited:

#### cgrablew

##### New Member
Ok but if you compare empty string to nothing they compare equal...If they are different why do they compare equal? Second how to you assign nothing to a cell in a formula?

#### sandy666

##### Banned - Rules violations
if you compare empty string to nothing they compare equal...If they are different why do they compare equal?
maybe they compare visual representation
how to you assign nothing to a cell in a formula?
you can't

Last edited:

Book1
ABCD
1 21
Sheet1
Cell Formulas
RangeFormula
A1A1=""
B1,D1B1=TYPE(A1)

#### cgrablew

##### New Member
What I'm trying to do is to create a Date Resolved Column. Some items are not resolved so it gets "". When I bring this into a pivot table, Excel doesn't recognize the column as date and doesn't give you the Month Grouping nor does it allow you to do the Grouping Manually. So based on what you said, I need to make the Date Resolved Column = to nothing. Can you suggest a way to do this or a workaround?

#### sandy666

##### Banned - Rules violations

use 0 instead of "" in formula. with date format it will give you 00/01/1900 which can be filtered later

#### cgrablew

##### New Member
thanks for your help....I believe this should be a bug.....Excel compare says empty = nothing is TRUE....it should be FALSE ... other functions can't return different answers on the cells if they cells compare equal

Thanks again

#### sandy666

##### Banned - Rules violations
that is why I said maybe they compare visual representation

Replies
3
Views
62
Replies
1
Views
394
Replies
0
Views
46
Replies
17
Views
227
Replies
1
Views
1K

1,130,055
Messages
5,639,798
Members
417,115
Latest member
hubbakong

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back