Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max date
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max date
Can you provide a sample of the dates contained in a cell?
In article . com, "philcud" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max date
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max date
:-
06 December 2004 03 February 2005 this will be in one cell, so you'll have to paste it into the formula bar. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max date
If you choose the technique that I posted earlier, it will also work
with the date format that you are using. Kostis Vezerides |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max date
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! In article .com, "philcud" wrote: :- 06 December 2004 03 February 2005 this will be in one cell, so you'll have to paste it into the formula bar. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find max date
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a date in a list | Excel Discussion (Misc queries) | |||
Another Date issue. | Excel Worksheet Functions | |||
how can i find a specific date of a database | Excel Discussion (Misc queries) | |||
search for latest date | Excel Worksheet Functions | |||
find date in Col A corresponding to min value in Col B | Excel Worksheet Functions |