Formula to show a Number 1 or 0 based on dates and numbers.

Try this

Please Login or Register  to view this content.

Plaese try
=(F18*F39=1)*(max(G24,L39,H51,H53,H55)<= Today())

Bo_RY,

Thank you. Your solution was closest to working best. I ended up modifying it to this:

Please Login or Register  to view this content.

But I realized I get a False "1" even when some cells are blank.

Is there a way to modify the formula to compensate?

V/r,
Jim

=(F18=1)*(F39=1)*(Max(G24,L39,H51,H53,H55)< =TODAY())

keep F18 & F39 separated to avoid 2 *1/2 = 1

What would you expect the result to be if any of them blank?

If you intent to input decimal in F18 and F39 (F18=1)*(F39=1) is a good precaution.
But I can see you use DV at F18 and F39 to -1, 0 ,1 so (F18*F39=1) is enough.

AND the dates in cells G24, L39, H51, H53, & H55 Dates are <= Today()

mean all date must be less than Today


Your formula
(MIN(G24,L39,H51,H53,H55)>=TODAY())
mean all date must be more than today

To check blank date

=(F18*F39=1)*(MIN(+G24,+L39,+H51,+H53,+H55)>= TODAY())

Bo_Ry,

Does it make a difference if the cells (G24,L39,H51,H53,H55) contains formulas?

For instance G24 contains =IFERROR(IF($F$24=0,0,EDATE($F$24,12)),"").

I modified the G24 cell to =IF($F$24="",0,EDATE($F$24,12)). It seems to work now.

V/r,
Jim

Yes, +G24 check real blank cell not blank by null string ""

to check null string "" try

=(F18*F39=1)*(MIN(G24,L39,H51,H53,H55)>= TODAY())*(count(G24,L39,H51,H53,H55)=5)