Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the earliest date from a range of cells
Hi,
From a range of cells in multiple columns with dates I am trying to get the earliest date listed in my summary row. I have managed to do this using the MIN function however I am having two problems with this, if the users have entered text in these columns then this function also gives me 00-Jan-00 in the summary column, I prefer that it does nto give me any value at all as this can be misleading, or if they provide the date using text such as Early Jan then the MIN function does not recognise it as a date as you can see in my eg. below. Then MIN function I used was MIN(A1:A3) for the first column and so on. E.g A B C 1 2-Jan-07 2-Sep-07 N/ A 2 6-Mar-08 Early Jan N/A 3 24-Dec-07 5-Feb-07 N/A Summary 2-Jan-07 5-Feb-07 00-Jan-00 I am not sure how to fix this. I appreciate any help! Ronia |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the earliest date from a range of cells
You can test the presence of an error by using the function ISERR which
returns TRUE if there is an error. So the formula could be: =IF(ISERR(A1:A3), "Put here text in case of error or simply two double quotes",Min(A1:A3)) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200707/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the earliest date from a range of cells
Try this:
=IF(COUNT(A1:A3),MIN(A1:A3),"") " wrote: Hi, From a range of cells in multiple columns with dates I am trying to get the earliest date listed in my summary row. I have managed to do this using the MIN function however I am having two problems with this, if the users have entered text in these columns then this function also gives me 00-Jan-00 in the summary column, I prefer that it does nto give me any value at all as this can be misleading, or if they provide the date using text such as Early Jan then the MIN function does not recognise it as a date as you can see in my eg. below. Then MIN function I used was MIN(A1:A3) for the first column and so on. E.g A B C 1 2-Jan-07 2-Sep-07 N/ A 2 6-Mar-08 Early Jan N/A 3 24-Dec-07 5-Feb-07 N/A Summary 2-Jan-07 5-Feb-07 00-Jan-00 I am not sure how to fix this. I appreciate any help! Ronia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the earliest date from a range of cells | Excel Worksheet Functions | |||
How do I find the earliest date? | Excel Discussion (Misc queries) | |||
finding earliest date within a range by employee | Excel Worksheet Functions | |||
Find the earliest date in a range of dates? | Excel Worksheet Functions | |||
Earliest Date to populate? | Excel Worksheet Functions |