Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the earliest date in a range of dates?
I am trying to use a function that will find the earliest date in a wide
range of dates. I have tried using =MIN(A1:A455) but it returns 0-Jan-00. There are no spaces in the range. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the earliest date in a range of dates?
I bet you have a 0 in that range.
It could be hidden by formatting (or conditional formatting), but it's there. If you select A1:A455 and edit|find search for 0 (Match entire cell contents) you may find it (or search for 0-jan-00 if it may be formatted as a date) If it turns out you have to leave the 0's in your data, you can use a different formula to ignore them: =MIN(IF(a1:a4550,a1:a455)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. ==== But I'd do my best to clean the data. Rachel Williams wrote: I am trying to use a function that will find the earliest date in a wide range of dates. I have tried using =MIN(A1:A455) but it returns 0-Jan-00. There are no spaces in the range. -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the earliest date in a range of dates?
Ps. That 0 could be on a hidden row, too???
Dave Peterson wrote: I bet you have a 0 in that range. It could be hidden by formatting (or conditional formatting), but it's there. If you select A1:A455 and edit|find search for 0 (Match entire cell contents) you may find it (or search for 0-jan-00 if it may be formatted as a date) If it turns out you have to leave the 0's in your data, you can use a different formula to ignore them: =MIN(IF(a1:a4550,a1:a455)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. ==== But I'd do my best to clean the data. Rachel Williams wrote: I am trying to use a function that will find the earliest date in a wide range of dates. I have tried using =MIN(A1:A455) but it returns 0-Jan-00. There are no spaces in the range. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sum a range if the date is is greater than today's date? | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Find last occurance of text in range | Excel Worksheet Functions | |||
Value between 2 dates | Excel Worksheet Functions | |||
Finding dates within a date range | Excel Worksheet Functions |