Finding min date value with hidden zeros with vlookup
 

Hi,
I'm trying to find the earliest date from 4 cells (B:E) that have time
format mm/dd/yyyy. Some or all of the 4 cells might be empty (hidden zeros).
First the simple min( ) function gave 1/0/1900 if the were any empty cells,
but I managed to get around that by using for example
=IF(AND(B2="",C2="",D2="",E2=""),"",MIN(B2:E2))

However, now I need to get the answer to a different worksheet using vlookup
function, but I started getting 1/0/1900 answers again. Any advice?

For example:
Worksheet1 looks like this
A B
Start
End
Plan

Worksheet2
A B C D E
Start 1/7/2009 11/4/2009 1/13/2009
End
Plan 8/1/2009 6/9/2009 4/8/2009

For Worksheet1 column B I want
1/7/2009
(empty cell or N/A)
4/8/2009

Thanks!

Finding min date value with hidden zeros with vlookup
 

Adapt this formula

=IF(MIN(B2:E2)=0,"",MIN(B2:E2))

--
__________________________________
HTH

Bob

"ML" wrote in message
...
Hi,
I'm trying to find the earliest date from 4 cells (B:E) that have time
format mm/dd/yyyy. Some or all of the 4 cells might be empty (hidden
zeros).
First the simple min( ) function gave 1/0/1900 if the were any empty
cells,
but I managed to get around that by using for example
=IF(AND(B2="",C2="",D2="",E2=""),"",MIN(B2:E2))

However, now I need to get the answer to a different worksheet using
vlookup
function, but I started getting 1/0/1900 answers again. Any advice?

For example:
Worksheet1 looks like this
A B
Start
End
Plan

Worksheet2
A B C D E
Start 1/7/2009 11/4/2009 1/13/2009
End
Plan 8/1/2009 6/9/2009 4/8/2009

For Worksheet1 column B I want
1/7/2009
(empty cell or N/A)
4/8/2009

Thanks!

Finding min date value with hidden zeros with vlookup
 

Thanks Bob, this got rid of the 1/0/1900 answers and changed those cells
empty. But I still want to capture the min date in cases when e.g. one of the
four cells is empty, but 3 cells have a date in them. In which case I want
the min of the three dates, ignoring the empty cell.
Thanks again!

"Bob Phillips" wrote:

Adapt this formula

=IF(MIN(B2:E2)=0,"",MIN(B2:E2))

--
__________________________________
HTH

Bob

"ML" wrote in message
...
Hi,
I'm trying to find the earliest date from 4 cells (B:E) that have time
format mm/dd/yyyy. Some or all of the 4 cells might be empty (hidden
zeros).
First the simple min( ) function gave 1/0/1900 if the were any empty
cells,
but I managed to get around that by using for example
=IF(AND(B2="",C2="",D2="",E2=""),"",MIN(B2:E2))

However, now I need to get the answer to a different worksheet using
vlookup
function, but I started getting 1/0/1900 answers again. Any advice?

For example:
Worksheet1 looks like this
A B
Start
End
Plan

Worksheet2
A B C D E
Start 1/7/2009 11/4/2009 1/13/2009
End
Plan 8/1/2009 6/9/2009 4/8/2009

For Worksheet1 column B I want
1/7/2009
(empty cell or N/A)
4/8/2009

Thanks!

Finding min date value with hidden zeros with vlookup
 

=max(min(B2:E2),0)

"ML" wrote:

Thanks Bob, this got rid of the 1/0/1900 answers and changed those cells
empty. But I still want to capture the min date in cases when e.g. one of the
four cells is empty, but 3 cells have a date in them. In which case I want
the min of the three dates, ignoring the empty cell.
Thanks again!

"Bob Phillips" wrote:

Adapt this formula

=IF(MIN(B2:E2)=0,"",MIN(B2:E2))

--
__________________________________
HTH

Bob

"ML" wrote in message
...
Hi,
I'm trying to find the earliest date from 4 cells (B:E) that have time
format mm/dd/yyyy. Some or all of the 4 cells might be empty (hidden
zeros).
First the simple min( ) function gave 1/0/1900 if the were any empty
cells,
but I managed to get around that by using for example
=IF(AND(B2="",C2="",D2="",E2=""),"",MIN(B2:E2))

However, now I need to get the answer to a different worksheet using
vlookup
function, but I started getting 1/0/1900 answers again. Any advice?

For example:
Worksheet1 looks like this
A B
Start
End
Plan

Worksheet2
A B C D E
Start 1/7/2009 11/4/2009 1/13/2009
End
Plan 8/1/2009 6/9/2009 4/8/2009

For Worksheet1 column B I want
1/7/2009
(empty cell or N/A)
4/8/2009

Thanks!

Finding min date value with hidden zeros with vlookup
 

Hi,

Here is another approach:
1. =MIN(B2:E2)
2. Choose Tools, Options, View, and uncheck Zero values

or
1. =MIN(B2:E2)
2. Select the range with the formulas and choose Format, Cells, Number tab,
Custom and enter the following format code on the Type line:
m/d/yyyy;;;

--
Thanks,
Shane Devenshire


"ML" wrote:

Hi,
I'm trying to find the earliest date from 4 cells (B:E) that have time
format mm/dd/yyyy. Some or all of the 4 cells might be empty (hidden zeros).
First the simple min( ) function gave 1/0/1900 if the were any empty cells,
but I managed to get around that by using for example
=IF(AND(B2="",C2="",D2="",E2=""),"",MIN(B2:E2))

However, now I need to get the answer to a different worksheet using vlookup
function, but I started getting 1/0/1900 answers again. Any advice?

For example:
Worksheet1 looks like this
A B
Start
End
Plan

Worksheet2
A B C D E
Start 1/7/2009 11/4/2009 1/13/2009
End
Plan 8/1/2009 6/9/2009 4/8/2009

For Worksheet1 column B I want
1/7/2009
(empty cell or N/A)
4/8/2009

Thanks!