|
Earlest Date Based On A Date Using Min Formula
I'm triyng to get the earlest date under DATE1 (coloumn A) based on a date
under DATE 2 (coloumn B) by using the MIN formula. DATE 1 DATE 2 4/30/2008 4/30/2008 4/30/2008 4/30/2008 4/29/2008 4/30/2008 4/28/2008 4/29/2008 4/27/2008 4/29/2008 =MIN(IF(B2:B6="4/30/2008",A2:A6)) 4/27/2008 is the answer I get. But it should be 4/29/2008. Can someone show the correct formula for this? -- Cue |
Earlest Date Based On A Date Using Min Formula
=MIN(IF(B2:B6=DATEVALUE("4/30/2008"),A2:A6))
Must be entered as a Array Formula (enter with CNTRL-SHFT-ENTER rather than just the ENTER key) -- Gary''s Student - gsnu200784 "Cue" wrote: I'm triyng to get the earlest date under DATE1 (coloumn A) based on a date under DATE 2 (coloumn B) by using the MIN formula. DATE 1 DATE 2 4/30/2008 4/30/2008 4/30/2008 4/30/2008 4/29/2008 4/30/2008 4/28/2008 4/29/2008 4/27/2008 4/29/2008 =MIN(IF(B2:B6="4/30/2008",A2:A6)) 4/27/2008 is the answer I get. But it should be 4/29/2008. Can someone show the correct formula for this? -- Cue |
Earlest Date Based On A Date Using Min Formula
It works for the data below. Thanks Gary. But I also have another similar
spreadsheet with about 15k rows of dates. Column A has blanks cells. So when I used the Array Formula, it gives me a value of 0. Any suggestions? -- Cue "Gary''s Student" wrote: =MIN(IF(B2:B6=DATEVALUE("4/30/2008"),A2:A6)) Must be entered as a Array Formula (enter with CNTRL-SHFT-ENTER rather than just the ENTER key) -- Gary''s Student - gsnu200784 "Cue" wrote: I'm triyng to get the earlest date under DATE1 (coloumn A) based on a date under DATE 2 (coloumn B) by using the MIN formula. DATE 1 DATE 2 4/30/2008 4/30/2008 4/30/2008 4/30/2008 4/29/2008 4/30/2008 4/28/2008 4/29/2008 4/27/2008 4/29/2008 =MIN(IF(B2:B6="4/30/2008",A2:A6)) 4/27/2008 is the answer I get. But it should be 4/29/2008. Can someone show the correct formula for this? -- Cue |
Earlest Date Based On A Date Using Min Formula
Try this...
A1 = your date variable Array entered: =MIN(IF((Date2=A1)*(Date1<""),Date1)) Format as Date -- Biff Microsoft Excel MVP "Cue" wrote in message ... It works for the data below. Thanks Gary. But I also have another similar spreadsheet with about 15k rows of dates. Column A has blanks cells. So when I used the Array Formula, it gives me a value of 0. Any suggestions? -- Cue "Gary''s Student" wrote: =MIN(IF(B2:B6=DATEVALUE("4/30/2008"),A2:A6)) Must be entered as a Array Formula (enter with CNTRL-SHFT-ENTER rather than just the ENTER key) -- Gary''s Student - gsnu200784 "Cue" wrote: I'm triyng to get the earlest date under DATE1 (coloumn A) based on a date under DATE 2 (coloumn B) by using the MIN formula. DATE 1 DATE 2 4/30/2008 4/30/2008 4/30/2008 4/30/2008 4/29/2008 4/30/2008 4/28/2008 4/29/2008 4/27/2008 4/29/2008 =MIN(IF(B2:B6="4/30/2008",A2:A6)) 4/27/2008 is the answer I get. But it should be 4/29/2008. Can someone show the correct formula for this? -- Cue |
Earlest Date Based On A Date Using Min Formula
It works. Thank you!
-- Cue "T. Valko" wrote: Try this... A1 = your date variable Array entered: =MIN(IF((Date2=A1)*(Date1<""),Date1)) Format as Date -- Biff Microsoft Excel MVP "Cue" wrote in message ... It works for the data below. Thanks Gary. But I also have another similar spreadsheet with about 15k rows of dates. Column A has blanks cells. So when I used the Array Formula, it gives me a value of 0. Any suggestions? -- Cue "Gary''s Student" wrote: =MIN(IF(B2:B6=DATEVALUE("4/30/2008"),A2:A6)) Must be entered as a Array Formula (enter with CNTRL-SHFT-ENTER rather than just the ENTER key) -- Gary''s Student - gsnu200784 "Cue" wrote: I'm triyng to get the earlest date under DATE1 (coloumn A) based on a date under DATE 2 (coloumn B) by using the MIN formula. DATE 1 DATE 2 4/30/2008 4/30/2008 4/30/2008 4/30/2008 4/29/2008 4/30/2008 4/28/2008 4/29/2008 4/27/2008 4/29/2008 =MIN(IF(B2:B6="4/30/2008",A2:A6)) 4/27/2008 is the answer I get. But it should be 4/29/2008. Can someone show the correct formula for this? -- Cue |
Earlest Date Based On A Date Using Min Formula
You're welcome!
-- Biff Microsoft Excel MVP "Cue" wrote in message ... It works. Thank you! -- Cue "T. Valko" wrote: Try this... A1 = your date variable Array entered: =MIN(IF((Date2=A1)*(Date1<""),Date1)) Format as Date -- Biff Microsoft Excel MVP "Cue" wrote in message ... It works for the data below. Thanks Gary. But I also have another similar spreadsheet with about 15k rows of dates. Column A has blanks cells. So when I used the Array Formula, it gives me a value of 0. Any suggestions? -- Cue "Gary''s Student" wrote: =MIN(IF(B2:B6=DATEVALUE("4/30/2008"),A2:A6)) Must be entered as a Array Formula (enter with CNTRL-SHFT-ENTER rather than just the ENTER key) -- Gary''s Student - gsnu200784 "Cue" wrote: I'm triyng to get the earlest date under DATE1 (coloumn A) based on a date under DATE 2 (coloumn B) by using the MIN formula. DATE 1 DATE 2 4/30/2008 4/30/2008 4/30/2008 4/30/2008 4/29/2008 4/30/2008 4/28/2008 4/29/2008 4/27/2008 4/29/2008 =MIN(IF(B2:B6="4/30/2008",A2:A6)) 4/27/2008 is the answer I get. But it should be 4/29/2008. Can someone show the correct formula for this? -- Cue |
| All times are GMT +1. The time now is 08:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com