Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 54
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Finding the earliest date from a range of cells

Try this:

=IF(COUNT(A1:A3),MIN(A1:A3),"")

Copy across as needed

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I find the earliest date? Dave Shultz Excel Discussion (Misc queries) 1 May 11th 07 06:45 PM
extracting earliest date (age) from list Dave Excel Discussion (Misc queries) 1 February 5th 07 06:27 PM
finding earliest date within a range by employee Steve Excel Worksheet Functions 4 October 19th 06 11:34 PM
Find the earliest date in a range of dates? Rachel Williams Excel Worksheet Functions 2 February 10th 06 03:50 PM
Earliest Date to populate? sonquizzon Excel Worksheet Functions 2 June 16th 05 07:35 PM


All times are GMT +1. The time now is 11:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"