Null Date Fields

[cgrablew]

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​

 

[sandy666]

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

 

[cgrablew]

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]

if you compare empty string to nothing they compare equal...If they are different why do they compare equal?

ask Microsoft
maybe they compare visual representation

how to you assign nothing to a cell in a formula?

you can't

 

[sandy666]

Book1
	A	B	C	D
1		2		1
Sheet1

Cell Formulas
Range	Formula
A1	A1	=""
B1,D1	B1	=TYPE(A1)

 

[cgrablew]

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]

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

 

[cgrablew]

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]

that is why I said maybe they compare visual representation