Forum Discussion
Empty cell is not empty
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 ( "" ).
- Joe
I am grateful
Clive
