Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Zeros | Excel Worksheet Functions | |||
finding a max date in a range in vlookup data | Excel Discussion (Misc queries) | |||
Vlookup blanks = zeros | Excel Discussion (Misc queries) | |||
Finding all cells that are locked/hidden | Excel Discussion (Misc queries) | |||
finding the no. of rows in a COL filled with numbers, zeros and bl | Excel Worksheet Functions |