Find max date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

philcud - 24 Nov 2005 15:38 GMT

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?

Reply to this Message

Domenic - 24 Nov 2005 16:06 GMT

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

> 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?

Reply to this Message

philcud - 24 Nov 2005 16:35 GMT

06 December 2004
03 February 2005

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

Reply to this Message

vezerid - 24 Nov 2005 16:50 GMT

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

Kostis Vezerides

Reply to this Message

Domenic - 24 Nov 2005 18:38 GMT

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!

> :-
> 06 December 2004
> 03 February 2005
>
> this will be in one cell, so you'll have to paste it into the formula
> bar.

Reply to this Message

vezerid - 24 Nov 2005 16:32 GMT

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

Reply to this Message

philcud - 25 Nov 2005 10:31 GMT

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)))

Reply to this Message