I am trying to write a formula to show the 'soonest' date out of 2 columns,
but if both the columns are blank then show blank.

So far I have:

=IF(AA3AB3, AA3, AB3)

Which correctly shows the date, but where there is blanks it is dragging
through a date of 00/01/1900.

Can anyone help me extend my formula?

Many Thanks

Helen

Try this in a cell formatted as date,

=IF(AND(ISNUMBER(AA3),ISNUMBER(AB3)),MIN(AA3,AB3), "")

Mike

"hjm" wrote:

I am trying to write a formula to show the 'soonest' date out of 2 columns,
but if both the columns are blank then show blank.

So far I have:

=IF(AA3AB3, AA3, AB3)

Which correctly shows the date, but where there is blanks it is dragging
through a date of 00/01/1900.

Can anyone help me extend my formula?

Many Thanks

Helen

Hi Mike,

Thanks for that, unfortuantely it is only bringing a result through where
there is a date in both Col AA & AB.
There isn't always data in both of these columns, but I need AC to show the
ssonest date or a blank.

Thanks again

H

"Mike" wrote:

Try this in a cell formatted as date,

=IF(AND(ISNUMBER(AA3),ISNUMBER(AB3)),MIN(AA3,AB3), "")

Mike

"hjm" wrote:

I am trying to write a formula to show the 'soonest' date out of 2 columns,
but if both the columns are blank then show blank.

So far I have:

=IF(AA3AB3, AA3, AB3)

Which correctly shows the date, but where there is blanks it is dragging
through a date of 00/01/1900.

Can anyone help me extend my formula?

Many Thanks

Helen

On Thu, 29 Mar 2007 04:04:00 -0700, hjm wrote:

I am trying to write a formula to show the 'soonest' date out of 2 columns,
but if both the columns are blank then show blank.

So far I have:

=IF(AA3AB3, AA3, AB3)

Which correctly shows the date, but where there is blanks it is dragging
through a date of 00/01/1900.

Can anyone help me extend my formula?

Many Thanks

Helen


=IF(MIN(A10,B10)=0,"",MIN(A10,B10))


--ron

Thanks Ron, thats great - works perfectly!

"Ron Rosenfeld" wrote:

On Thu, 29 Mar 2007 04:04:00 -0700, hjm wrote:

I am trying to write a formula to show the 'soonest' date out of 2 columns,
but if both the columns are blank then show blank.

So far I have:

=IF(AA3AB3, AA3, AB3)

Which correctly shows the date, but where there is blanks it is dragging
through a date of 00/01/1900.

Can anyone help me extend my formula?

Many Thanks

Helen


=IF(MIN(A10,B10)=0,"",MIN(A10,B10))


--ron

On Thu, 29 Mar 2007 05:22:04 -0700, hjm wrote:

Thanks Ron, thats great - works perfectly!

You're welcome. Thanks for the feedback.
--ron