Forum Discussion
Empty cell is not empty
I have a sheet with three columns:
colA colB colC
colA and colB should show a date and are formatted to xx/xx/xx e.g: 12/03/24 or there may be no date.
There is a formula that will populate or make zero colA: =IFERROR(INDEX($B$2:$BN$2,AGGREGATE(15,6,COLUMN($B$2:$BN$2)/(L8:BX8<>""),1)-1),)
If this formula produces a date all is well. But if there is no date to show in colA I see this: 00/01/00
So yes, I turn off 'show zeros' in preferences. But the formula in colC which is designed to consolidate colA and colB sees this "zero" date result and fails. By failure I mean nothing shows in colC when the correct result in colC should be the content of colB
Clive_Rabson wrote: "But if there is no date to show in colA I see this: 00/01/00"
Because you wrote IFERROR( ... , ), and the second IFERROR defaults to zero when it is omitted, and you formatted the cell as a date form, e.g. dd/mm/yy.
(Excel displays zero as the bogus date 0 Jan 1900.)
-----
Clive_Rabson wrote: "should show a date [...] or there may be no date"
The formula can be =IFERROR( ... , "" ).
But in that case, the result is a string, not numeric. That might complicate things in dependent cells.
Alternatively, leave the formula as you wrote it -- although I prefer
=IFERROR( ... , 0 ) instead of omitting the parameter -- and format the cell as
Custom [=0]"";dd/mm/yy .
In that case, the cell value is actually zero, but Excel displays the null string; that is, it appears blank.
Joe
Thank you.
The CellBX3 formula =MIN(BP3,BT3) still does not pick up the date showing in CellBT3 because, I assume, it sees something in BP3, even though that cell is now formatted to [=0]"";dd/mm/yy.
I have also changed the end of the BP3 formula to ....................,0) as you suggested. So it is now: =IFERROR(INDEX($B$2:$BN$2,AGGREGATE(15,6,COLUMN($B$2:$BN$2)/(B3:BN3<>""),1)-1),0)
I have attached some screen grabs that might show the situation in those different cellsClive_Rabson wrote: "MIN(BP3,BT3) still does not pick up the date showing in CellBT3 [....] I have also changed the end of the BP3 [...] as you suggested. So it is now: =IFERROR( [...] , 0 )"
I also suggested =IFERROR( [...] , "" )
You might choose that alternative if you want MIN to ignore BP3 when IFERROR detects an error.
Excel dates are stored as integers; namely, the number of days after 31 Dec 1899. Thus, 1 Jan 1900 is 1. And 21 Jun 2023 is 45098.
So, when BP3 is zero (even though it appears to be blank due the cell format), obviously zero is "the MIN of" (less than) any legitimate date in BT3.
However, MIN ignores cells whose value is text, including the null string ( "" ).
