MINimum Function and blank/empty cells

[ArPharazon]

I have seen references here to formulas like this =MIN(IF(A1:A3="",0,A1:A3))

What I'm doing is this =MIN(G2,I2)

I want the minimum value (earliest date/time) from one cell or the other which are in date/time (mm/dd/yyyy hh:mm) format.

The only issue is, it represents 2 blank cells as 01/00/1900 00:00 instead of a blank cell.

Anything I can do with the formula?

 

[dreid1011]

Because MIN() returns 0 with all blanks, and since your formula cell is formatted as date, that results in 1/0/1900.

You could wrap it like this:

=IF(MIN(G2,I2),MIN(G2,I2),"")

 

[ArPharazon]

Because MIN() returns 0 with all blanks, and since your formula cell is formatted as date, that results in 1/0/1900.

You could wrap it like this:

=IF(MIN(G2,I2),MIN(G2,I2),"")

Thanks. That does work, though I have to format the result as mm/dd/yyyy hh:mm,
whereas the simpler formula did that automatically.

 

[dreid1011]

Ah, well as long as it works right? I don't know if there is another way that it might carry over the formatting.

 

[ArPharazon]

Ah, well as long as it works right? I don't know if there is another way that it might carry over the formatting.

There is. Someone here at work showed me a more complicated formula.

=IF(COUNTBLANK([@[Finish Date]])+COUNTBLANK([@[Completion Date]])=2,"",MIN(G2,I2))

My hope is there won't be any unfinished/uncompleted Work Orders on this particular Task any more, but I'm not sure that will ever happen.

 

[dreid1011]

Glad you got a working solution then. Curious though how one works one way and the other works different. They both effectively return a "" or MIN(G2,I2), so why one adopts the formatting and the other doesn't... it is beyond me.

 

[ArPharazon]

Glad you got a working solution then. Curious though how one works one way and the other works different. They both effectively return a "" or MIN(G2,I2), so why one adopts the formatting and the other doesn't... it is beyond me.

I'd just chalk it up to being an "Excel thing". Cell formatting can be a real PITA most of the time. I'm surprised it automatically formats that data as date/time at all.

It's nice to know a have a couple options.

 

[dreid1011]

Indeed.