Identify the latest date from the list

[kaushik03]

Hi all,


I have list of dates from which I wnat to identify the latest date. But the dates are in date and time format in the cell.(this is a system generated information). I am looking for a single formula to get the result.


8/13/2012 2:10:18 PM

8/13/2012 9:14:52 AM

8/10/2012 9:22:42 AM

8/9/2012 10:41:26 AM

8/8/2012 4:59:38 PM

8/7/2012 8:05:55 AM

8/7/2012 6:59:54 AM

8/6/2012 2:36:39 PM

8/6/2012 10:45:50 AM

8/6/2012 8:54:34 AM

8/6/2012 1:48:19 AM

8/3/2012 9:51:42 AM

8/2/2012 1:41:03 PM

8/2/2012 11:56:40 AM

8/2/2012 2:28:04 AM

8/1/2012 1:29:33 PM

8/1/2012 11:22:15 AM

8/1/2012 10:50:46 AM

8/1/2012 8:03:35 AM

7/30/2012 12:43:20 PM

7/30/2012 11:45:44 AM

7/25/2012 9:02:30 AM

7/20/2012 6:02:46 PM

7/18/2012 11:01:27 AM

7/18/2012 10:15:31 AM

7/13/2012 7:56:58 AM

7/13/2012 7:11:49 AM

7/12/2012 8:10:49 PM

7/10/2012 6:08:24 AM

7/9/2012 12:36:41 PM

7/6/2012 1:58:34 PM

7/6/2012 10:13:23 AM

7/6/2012 7:00:34 AM

7/5/2012 2:19:35 PM

7/5/2012 10:21:39 AM


I tried with the combination of text formula (to extract the date part) and max formula(to identify the latest date) but not able to make it work in the right way.


Looking forward to your help.


Kaushik

 

[mercatog]

Hi

If your dates in A1:A100, try this:

=INT(MAX(A1:A100))

And formate your cell to mm/dd/yyyy.

Regards

 

[kaushik03]

Hi Mercatog,


Thank you for your quick reply.


But I am getting 01/00/1900 as the output.


When I tried earlier with text and match formula, I also got the same result.


Please not that, since the dates are system generated, excel stores the date under general format in the column where it is imported.


Please advise.


Kaushik

 

[Luke M]

Sounds like the data is actually being stored as a text string, and we'll need to first force it to become numbers. Try this array formula (Ctrl+Shift+Enter):

=INT(MAX(A1:A100*1))


Alternatively, to fix the data itself, put the number 1 into a cell somewhere. Copy that cell, select your data in A1:A100, and do a Paste Special - multiply. This should force the text strings into numbers.

 

[kaushik03]

Thank you Luke...awesome.


I also come up with an approach that also works. Here it is


MAX(DATEVALUE('Recurreing Clients'!T:T))


where 'Recurreing Clients' is the sheet name and the dates are stored in col T


Thank you all for your help


Kaushik