| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find Oldest Date
I need to fiind the oldest date of an entry depending on the status of the
case. A B C Canada Open 6/25/07 Mexico Open 6/16/07 Brazil Open 6/1/07 Canada Open 6/19/07 Canada Closed 6/2/07 I need to be able find the oldest open date for Canada which would be 6/19/07 according to this example. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Oldest Date
=MIN(IF((A1:A5="Canada")*(B1:B5="Open"),C1:C5))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Qaspec" wrote in message ... I need to fiind the oldest date of an entry depending on the status of the case. A B C Canada Open 6/25/07 Mexico Open 6/16/07 Brazil Open 6/1/07 Canada Open 6/19/07 Canada Closed 6/2/07 I need to be able find the oldest open date for Canada which would be 6/19/07 according to this example. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Oldest Date
Try this array formula** :
=MIN(IF((A1:A5="Canada")*(B1:B5="Open"),C1:C5)) Format as DATE Better to use cells to hold the criteria: E1 = Canada F1 = Open =MIN(IF((A1:A5=E1)*(B1:B5=F1),C1:C5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Qaspec" wrote in message ... I need to fiind the oldest date of an entry depending on the status of the case. A B C Canada Open 6/25/07 Mexico Open 6/16/07 Brazil Open 6/1/07 Canada Open 6/19/07 Canada Closed 6/2/07 I need to be able find the oldest open date for Canada which would be 6/19/07 according to this example. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Oldest Date
The formula works except for the fact that unfortunately some of the records
have null values in the date field. How do I ignore those and find the oldest entered date? I'm assuming I keep returning 1/0/1900 because of the blank records. "Bob Phillips" wrote: =MIN(IF((A1:A5="Canada")*(B1:B5="Open"),C1:C5)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Qaspec" wrote in message ... I need to fiind the oldest date of an entry depending on the status of the case. A B C Canada Open 6/25/07 Mexico Open 6/16/07 Brazil Open 6/1/07 Canada Open 6/19/07 Canada Closed 6/2/07 I need to be able find the oldest open date for Canada which would be 6/19/07 according to this example. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Oldest Date
try:
=MIN(IF((A1:A8="Canada")*(B1:B8="Open")*ISNUMBER(C 1:C8),C1:C8)) "Qaspec" wrote: The formula works except for the fact that unfortunately some of the records have null values in the date field. How do I ignore those and find the oldest entered date? I'm assuming I keep returning 1/0/1900 because of the blank records. "Bob Phillips" wrote: =MIN(IF((A1:A5="Canada")*(B1:B5="Open"),C1:C5)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Qaspec" wrote in message ... I need to fiind the oldest date of an entry depending on the status of the case. A B C Canada Open 6/25/07 Mexico Open 6/16/07 Brazil Open 6/1/07 Canada Open 6/19/07 Canada Closed 6/2/07 I need to be able find the oldest open date for Canada which would be 6/19/07 according to this example. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Oldest Date
I tried each of the suggestions and I'm still returning a 0 value. Could
there be something wrong with the formatting of the date in the referenced cell? "T. Valko" wrote: Try this array formula** : =MIN(IF((A1:A5="Canada")*(B1:B5="Open"),C1:C5)) Format as DATE Better to use cells to hold the criteria: E1 = Canada F1 = Open =MIN(IF((A1:A5=E1)*(B1:B5=F1),C1:C5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Qaspec" wrote in message ... I need to fiind the oldest date of an entry depending on the status of the case. A B C Canada Open 6/25/07 Mexico Open 6/16/07 Brazil Open 6/1/07 Canada Open 6/19/07 Canada Closed 6/2/07 I need to be able find the oldest open date for Canada which would be 6/19/07 according to this example. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Oldest Date
Check the dates are true DATES and not TEXT.
"Qaspec" wrote: I tried each of the suggestions and I'm still returning a 0 value. Could there be something wrong with the formatting of the date in the referenced cell? "T. Valko" wrote: Try this array formula** : =MIN(IF((A1:A5="Canada")*(B1:B5="Open"),C1:C5)) Format as DATE Better to use cells to hold the criteria: E1 = Canada F1 = Open =MIN(IF((A1:A5=E1)*(B1:B5=F1),C1:C5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Qaspec" wrote in message ... I need to fiind the oldest date of an entry depending on the status of the case. A B C Canada Open 6/25/07 Mexico Open 6/16/07 Brazil Open 6/1/07 Canada Open 6/19/07 Canada Closed 6/2/07 I need to be able find the oldest open date for Canada which would be 6/19/07 according to this example. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find Oldest Date
=MIN(IF((A1:A5="Canada")*(B1:B5="Open")*(C1:C5<"" ),C1:C5))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Qaspec" wrote in message ... I tried each of the suggestions and I'm still returning a 0 value. Could there be something wrong with the formatting of the date in the referenced cell? "T. Valko" wrote: Try this array formula** : =MIN(IF((A1:A5="Canada")*(B1:B5="Open"),C1:C5)) Format as DATE Better to use cells to hold the criteria: E1 = Canada F1 = Open =MIN(IF((A1:A5=E1)*(B1:B5=F1),C1:C5)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Qaspec" wrote in message ... I need to fiind the oldest date of an entry depending on the status of the case. A B C Canada Open 6/25/07 Mexico Open 6/16/07 Brazil Open 6/1/07 Canada Open 6/19/07 Canada Closed 6/2/07 I need to be able find the oldest open date for Canada which would be 6/19/07 according to this example. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| oldest date not completed | Excel Worksheet Functions | |||
| Special sort for oldest and newest date | Excel Discussion (Misc queries) | |||
| Lookup Oldest date from previously selected Group Data | Excel Worksheet Functions | |||
| Need to find oldest date in ever changing list. | Excel Worksheet Functions | |||
| Detecting Oldest Date On Spreadsheet | Excel Discussion (Misc queries) | |||
Linear Mode
