Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find the earliest dates in a range of dates?
In order to do what I ask, I know that I am supposed to use the MIN
function, but there is a twist to what I want to do. I have a set of data where each row is coded with a number (01, 02, 03... 10, 11, 12, etc.). Each row has a date associated with it. I want to find out is the earliest start date of all the rows coded "01", the earliest start date of all the rows coded "02", etc. Does anyone know how this can be done? (NOTE: My "Code" column is column C and is named "Code". My "Dates" column is column H and is named "Dates".) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find the earliest dates in a range of dates?
You could list your codes somewhere - let's assume column M with "01"
in M1, "02" in M2 etc. Then in N1 you could apply this array* formula: =MIN(IF(Code=M1,Dates,40000)) * As this is an array formula, once you have typed it in (or subsequently edit it) you must use CTRL-SHIFT-ENTER instead of just ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula - you must not type these yourself. You can copy the formula down column N for as many codes as you have in column M. Hope this helps. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find the earliest dates in a range of dates?
What does the 40000 represent? How would that change if I wanted to
find the latest date? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I find the earliest dates in a range of dates?
Dates are stored internally in Excel as the number of days since a
reference date (1st Jan 1900), so really they are just integers. Today's date is effectively 38853. Hence 40000 is much larger than this, and so will never be included as the minimum. If you wanted the latest date you would use MAX and replace the 40000 with 0, i.e.: =MAX(IF(Code=M1,Dates,0)) This could go into cell O1, committed with CTRL-SHIFT-ENTER again, and copied down. Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I select from within a range of dates? | Excel Discussion (Misc queries) | |||
Formula to retrieve range of dates from a worksheet to calculate d | Excel Worksheet Functions | |||
Find a "date" in a column of dates in Excel 2000 | Excel Worksheet Functions | |||
find a cells from a range of cell | Excel Worksheet Functions | |||
Use MATCH to find position of max in 2D range? | Excel Worksheet Functions |