Find max date

[philcud]

Hi all,
i have a number of dates in one cell separated by carriage returns
(alt-enter). how can i find the max of these dates?

 

[Domenic]

Can you provide a sample of the dates contained in a cell?

 

[vezerid]

I cannot think of a way to do it that does not involve either VBA or
helper columns. The technique I am suggesting here will involve
creating a column with the individual dates, so that you can then get
the maximum. Assuming the dates are in A1.

In B1:
=FIND(CHAR(10),A1)
In B2:
=FIND(CHAR(10), $A$1, B1+1)
In C1:
=DATE(YEAR(VALUE(LEFT(A1,B1-1))), MONTH(VALUE(LEFT(A1,B1-1))),
DAY(VALUE(LEFT(A1,B1-1))))
In C2:
=DATE(YEAR(VALUE(MID($A$1,B1+1,B2-B1-1))),
MONTH(VALUE(MID($A$1,B1+1,B2-B1-1))),
DAY(VALUE(MID($A$1,B1+1,B2-B1-1))))

Copy down the formulas in B2 and C2 until you get #VALUE!. You can then
get the max of column C:C.

HTH
Kostis Vezerides

 

[philcud]

:-
06 December 2004
03 February 2005

this will be in one cell, so you'll have to paste it into the formula
bar.

 

[vezerid]

If you choose the technique that I posted earlier, it will also work
with the date format that you are using.

Kostis Vezerides

 

[Domenic]

If you change the format of your dates, you'll be able to find the
maximum for the number of dates entered in one cell using a single
formula. So, for example, if you change your dates to the following
format...

06 Dec 2005
03 Feb 2005
15 Mar 2005

....you can use the following formula that needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=MAX(MID(SUBSTITUTE(A1,CHAR(10),""),(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,
CHAR(10),""))/11))*11-11)+1,11)+0)

If you have a column of cells that need to be changed into this format,
use FIND/REPLACE for each month...

1) Select/highlight your column or range of cells

2) Edit > Replace

Find what: December

Replace with: Dec

3) Click Replace All

4) Repeat for each month

Hope this helps!

 

[philcud]

unfortunately the way the original spreadsheet is set out it is not
suitable to add 'helper cells' on each row. ive checked with the data
owner and they say its ok to use the first date in a cell, so ended up
using a sumproduct to do counts of dates:-
=SUMPRODUCT(--(((IF(ISERROR(LEFT('Input Data'!$J$7:$J$26,(FIND("
",'Input Data'!$J$7:$J$26)-1))*1),'Input
Data'!$J$7:$J$26,LEFT('Input Data'!$J$7:$J$26,(FIND("
",'Input Data'!$J$7:$J$26)-1))*1))>=A2)*((IF(ISERROR(LEFT('Input
Data'!$J$7:$J$26,(FIND("
",'Input Data'!$J$7:$J$26)-1))*1),'Input
Data'!$J$7:$J$26,LEFT('Input Data'!$J$7:$J$26,(FIND("
",'Input Data'!$J$7:$J$26)-1))*1))<A2+7)))