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. |
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 |
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 |
All times are GMT +1. The time now is 04:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com