Works !!!
Thanx
Works !!!
Thanx
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.
"Maxi" <[email protected]> wrote in message
news:[email protected]...
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
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
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
Hi!
Try this....
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=MAX(DAY(A2:G2))
Biff
"Maxi" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
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
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
<[email protected]> wrote in message
news:[email protected]...
>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
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
"Maxi" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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?
"Maxi" <[email protected]> wrote in message
news:[email protected]...
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
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
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
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
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
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
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
Works !!!
Thanx
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
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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
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
"Maxi" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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
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
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
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
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
<[email protected]> wrote in message
news:[email protected]...
>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
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
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
Hi!
Try this....
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=MAX(DAY(A2:G2))
Biff
"Maxi" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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.
"Maxi" <[email protected]> wrote in message
news:[email protected]...
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
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?
"Maxi" <[email protected]> wrote in message
news:[email protected]...
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
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?
"Maxi" <[email protected]> wrote in message
news:[email protected]...
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
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
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
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
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
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
"Maxi" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
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
<[email protected]> wrote in message
news:[email protected]...
>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
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
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
Hi!
Try this....
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=MAX(DAY(A2:G2))
Biff
"Maxi" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
Works !!!
Thanx
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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
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.
"Maxi" <[email protected]> wrote in message
news:[email protected]...
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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
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?
"Maxi" <[email protected]> wrote in message
news:[email protected]...
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
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.
"Maxi" <[email protected]> wrote in message
news:[email protected]...
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, 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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
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
Hi!
Try this....
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=MAX(DAY(A2:G2))
Biff
"Maxi" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
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
"Maxi" <[email protected]> wrote in message
news:[email protected]...
> 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
>
Works !!!
Thanx
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
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
<[email protected]> wrote in message
news:[email protected]...
>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
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
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
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
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
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
Works !!!
Thanx
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.
"Maxi" <[email protected]> wrote in message
news:[email protected]...
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
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
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
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
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?
"Maxi" <[email protected]> wrote in message
news:[email protected]...
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
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
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
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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
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
"Maxi" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
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
<[email protected]> wrote in message
news:[email protected]...
>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
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
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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
Hi!
Try this....
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=MAX(DAY(A2:G2))
Biff
"Maxi" <[email protected]> wrote in message
news:[email protected]...
> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks