Null Date Fields

cgrablew

New Member
Joined
Nov 14, 2007
Messages
20
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

A1B1C1D1E1F1G1H1
FormulaTextNOT FORMULA=IF(C3=0,"")NOT FORMULA=D3=E3=MONTH(D3)=MONTH(E3)
Value/Formual
0​
TRUE​
#VALUE!​
1​
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
"" 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
Joined
Nov 14, 2007
Messages
20
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
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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

cgrablew

New Member
Joined
Nov 14, 2007
Messages
20
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
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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

cgrablew

New Member
Joined
Nov 14, 2007
Messages
20
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
Joined
Oct 24, 2015
Messages
7,499
that is why I said maybe they compare visual representation
 

Watch MrExcel Video

Forum statistics

Threads
1,130,095
Messages
5,640,081
Members
417,126
Latest member
Jeffman52

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top