View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_] Bob Phillips[_3_] is offline
external usenet poster
 
Posts: 2,420
Default 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!