Forum Discussion
Empty cell is not empty
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 cells
Clive_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 ( "" ).
- Clive_RabsonJun 02, 2024Copper ContributorJoe
I am grateful
Clive