Max date

[Maxi]

This is what I have in range A2:G2
9-May 17-May 1-Jun 15-Jun 13-Jul 14-Jul 18-Jul 18
13-May 6-Jun 12-Jun 20-Jun 1-Jul 16-Jul 20-Jul 20

I want to see the max date monthwise and then find out the max of all
the dates.

Eg. In Row 1 max figure for May is 17, June 15, July 18 hence Answer =
18
In Row 1 max figure for May is 13, June 20, July 20 hence Answer = 20

Maxi

 

[Maxi]

Sorry. I framed the question incorrectly.

I want to check the first occurring date of each month and then compare
it with the next occurring first date of the next month in the array
and then find out the max date.

Eg.
First row:
First date in May 9th, in June 1st, in Jul 13th hence the result should
be 13

Second row:
First date in May 13th, in June 6th, in Jul 1st hence the result should
be 13

Maxi

 

[kk]

Hi Maxi,

Assume your data range A1:C1

In D1, try this array formula
=MAX(DAY($A1:$C1))

Confirm the formula by Ctrl+Shift+Enter

Does it help?


Sorry. I framed the question incorrectly.

I want to check the first occurring date of each month and then compare
it with the next occurring first date of the next month in the array
and then find out the max date.

Eg.
First row:
First date in May 9th, in June 1st, in Jul 13th hence the result should
be 13

Second row:
First date in May 13th, in June 6th, in Jul 1st hence the result should
be 13

Maxi

 

[Maxi]

You didn't understand my question I guess

I am looking for a formula in cell I1 and I2

First of all there are two records.

Row1:
There are two dates for May and 9th May is the first date = 9.
There are two dates for Jun and 1st June is the first date = 1.
There are three dates for July and 13th July is the first date = 13.
Therefore the max value is 13 (out of 9 1 and 13) so I1 will contain 13

Row2:
There is one date for May and 13th May is the first date = 13.
There are three dates for Jun and 6th June is the first date = 6.
There are three dates for July and 1st July is the first date = 1.
Therefore the max value is 13 (out of 13 6 and 1) so I1 will contain 13

Maxi

 

[Biff]

Hi!

Try this....

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX(DAY(A2:G2))

Biff

 

[Maxi]

No Biff, the answer is not 20.

I am looking for an answer that give 13. Look at the corrected
question. The first question was incorrectly framed.

Maxi

 

[kk]

Hi!

I didn't read you first post so I assume your data range is A1:C1

In I1, type =Max(Day($A2:$G2))

This is an array formula so you need to confirmed it by Ctrl+Shift+Enter.



You didn't understand my question I guess

I am looking for a formula in cell I1 and I2

First of all there are two records.

Row1:
There are two dates for May and 9th May is the first date = 9.
There are two dates for Jun and 1st June is the first date = 1.
There are three dates for July and 13th July is the first date = 13.
Therefore the max value is 13 (out of 9 1 and 13) so I1 will contain 13

Row2:
There is one date for May and 13th May is the first date = 13.
There are three dates for Jun and 6th June is the first date = 6.
There are three dates for July and 1st July is the first date = 1.
Therefore the max value is 13 (out of 13 6 and 1) so I1 will contain 13

Maxi

 

[Maxi]

There is a big confusion.

Let me start over again.......... Now don't look at any previous
posts.... This is the final and only read this one.

This is what I have in range A2:G2
9-May 17-May 1-Jun 15-Jun 13-Jul 14-Jul 18-Jul 13
13-May 6-Jun 12-Jun 20-Jun 1-Jul 16-Jul 20-Jul 13

I want to check the first occurring date of each month and then compare
it with the next occurring first date of the next month and so on and
then find out the max date.

Eg.
First row:
First date in May 9th, in June 1st, in Jul 13th hence the result should
be 13
Second row:
First date in May 13th, in June 6th, in Jul 1st hence the result should

be 13

To make it more simpler:
Row1:
There are two dates for May and 9th May is the first date = 9.
There are two dates for Jun and 1st June is the first date = 1.
There are three dates for July and 13th July is the first date = 13.
Therefore the max value is 13 (out of 9 1 and 13) so I1 will contain 13


Row2:
There is one date for May and 13th May is the first date = 13.
There are three dates for Jun and 6th June is the first date = 6.
There are three dates for July and 1st July is the first date = 1.
Therefore the max value is 13 (out of 13 6 and 1) so I1 will contain 13


The answer in I1 and I2 both should come to 13

Maxi

 

[Biff]

Hi!

I understand what you want. It's not so simple!

Will the months always be the same? May, June and July?

Will the dates always be in ascending order?

Biff

 

[Maxi]

No, months can be different. There can be a month with 2004 as year and
same month with 2005. Yes, dates will always be in ascending order.

If not a formula, even a VBA would do. I am tired of thinking a logic
on how to get this issue resolved.

Maxi

 

[Biff]

Hi!

Here's one way but requires a helper row...

Dates in A2:G2

In A3 enter this formula and copy across to G3:

=IF(SUMPRODUCT(--(MONTH($A2:A2)=MONTH(A2)))>1,"",1)

Formula for the max day (still an array formula):

=MAX(DAY(IF(A3:G3=1,A2:G2)))

You can use this until someone comes up with something better. I'll keep
trying for something better that doesn't need a helper. I'm close!

Biff

 

[Maxi]

Good logic but as you said without the helper would be an efficient way
of doing it. Thanks for your efforts even this formula will work out.
Please let me know if you get something better.

Maxi

 

[Robert_Steel]

I have also tried to do this as an array and drawn a blank
however a UDF if it would help

Function MaxDate(myRange As Range) As Integer

Dim myarray(1 To 12) As Integer
For Each cell In myRange
If myarray(Month(cell.Value)) <> 0 Then
myarray(Month(cell.Value)) = _
WorksheetFunction.Min(Day(cell.Value),
myarray(Month(cell.Value)))
Else
myarray(Month(cell.Value)) = Day(cell.Value)
End If
Next cell

MaxDate = WorksheetFunction.Max(myarray)

End Function

Feels like brute force though

hth RES

 

[Biff]

Hi!

Don't know anything about VBA but when I try this I get a compile syntax
error with this section highlighted:

myarray(Month(cell.Value)) = _
WorksheetFunction.Min(Day(cell.Value),

Biff

 

[Biff]

Hi!

Not real elegant but it works without the need for the helper:

Dates in A2:G2

Array entered:

=MAX(DAY(MIN(IF(MONTH(A2:G2)=MONTH(A2),A2:G2))),DAY(MIN(IF(MONTH(A2:G2)=MONTH(B2),A2:G2))),DAY(MIN(IF(MONTH(A2:G2)=MONTH(C2),A2:G2))),DAY(MIN(IF(MONTH(A2:G2)=MONTH(D2),A2:G2))),DAY(MIN(IF(MONTH(A2:G2)=MONTH(E2),A2:G2))),DAY(MIN(IF(MONTH(A2:G2)=MONTH(F2),A2:G2))),DAY(MIN(IF(MONTH(A2:G2)=MONTH(G2),A2:G2))))

If the months are always a series of three and they're consecutive:

=MAX(DAY(MIN(IF(MONTH(A2:G2)=MONTH(A2),A2:G2))),DAY(MIN(IF(MONTH(A2:G2)=MONTH(A2)+1,A2:G2))),DAY(MIN(IF(MONTH(A2:G2)=MONTH(A2)+2,A2:G2))))

If you don't mind hardcoding the months:

=MAX(DAY(MIN(IF(MONTH(A2:G2)=5,A2:G2))),DAY(MIN(IF(MONTH(A2:G2)=6,A2:G2))),DAY(MIN(IF(MONTH(A2:G2)=7,A2:G2))))

Couldn't figure out how do do this as a single array statement.

Biff

 

[Robert_Steel]

Biff
The problem is due to the wonderful line wrapping that happens with a post

myarray(Month(cell.Value)) = _
WorksheetFunction.Min(Day(cell.Value),
myarray(Month(cell.Value)))

is all one line of code.
The _ tells VBA to complie the next line as part of the current.
Try it as

myarray(Month(cell.Value)) = _
WorksheetFunction.Min(Day(cell.Value), _
myarray(Month(cell.Value)))

Hopefully the newsgroup engines will not introduce any unforeseen wraps

 

[Maxi]

Biff, I appreciate your efforts and all is working fine but the UDF
from Robert is an efficient way of doing it.

As far as speed of processing is concerned, I am not sure if UDF takes
long time of the array formula.

Robert, one question for you.

I didn't understand the line Dim myarray(1 To 12) As Integer

Why 1 to 12?

Also the data what I gave was just a sample data. I have around 3160
rows in my file. When I used Biff's suggestion, everything works fine
but your UDF does not give me correct results for 366 rows out of 3160.


In my file I have 3160 rows and 25+ columns. There can be less than 25
columns in a row or there can be more. Few columns are BLANK but I
guess your If myarray(Month(cell.Value)) <> 0 Then line will take care
of it.

I am just wondering why I am not getting correct result for 366 rows.
Do I have to make changes in the Dim myarray(1 To 12) As Integer line?

Maxi

 

[Robert_Steel]

Maxi
The array

Dim myarray(1 To 12) As Integer

is equivalent to myarray(jan to dec) and stores a day value in each.
The reason you were getting incorrect results from the UDF I posted is I
had not protected against blank cells
A revised version is posted below.

The logic is as follows
Set up a data store for each possible month to hold the day value
loop through each cell in the range in turn
if there is a positive value in the cell continue if not skip to the next
cell
using the month value of the cell look in the array
if the entry in the array is not 0 then make it equal to the lowest of
either the current array value or the day value of the cell
otherwise put in the day value of the cell
when all the cells have been checked return the largest value in the array


***************************

Function MaxDate(myRange As Range) As Integer

Dim myarray(1 To 12) As Integer
For Each cell In myRange
If cell.Value > 0 Then
If myarray(Month(cell.Value)) <> 0 Then
myarray(Month(cell.Value)) = _
WorksheetFunction.Min(Day(cell.Value), _
myarray(Month(cell.Value)))
Else
myarray(Month(cell.Value)) = Day(cell.Value)
End If
End If
Next cell

MaxDate = WorksheetFunction.Max(myarray)

End Function

**************************

I am sure some of the gurus could simplify the logic but for now I hope
this does the trick.

let us know if it works
cheers RES

 

[Maxi]

Works !!!

Thanx