Excel: Largest Date for each month

[Salut39]

Hi Guys,

I have a list of dates and I need to pick up the largest date for every month. You can do it by sorting but I was wondering if there is another way for unsorted data.

Cheers,
Yuri

 

[Rofeu]

Hi,

put the month in A1 (i.e. as 01/08/2006) and the list to look in in D. Then use the following array-formula:

{=MAX((TEXT(D110,"YYYYMM")=TEXT(A1,"YYYYMM"))*D110)}

Cheers,

Roel

 

[Fenrirshowl]

Also see the DMax function using criteria of ascending months and (if required) years

D

 

[Salut39]

I tried DMAX and not sure what I should use for "Field"?

 

[Salut39]

Thanks Roel your formula is working great.

Yuri

 

[Fenrirshowl]

Yuri

Glad its sorted, but for reference:

DMAX

If you have several years and you need the months for each year:
1) Add a column for each (entitled Month and Year, using the respective functions to calculate the value). I will assume these are in columns A to C
2) Add a criteria table of years and month (both ascending) in columns E & F entitled Year and Month (as per the main table), i.e.
Year Month
2006 11
2006 12
2007 1
etc
3) Next to the criteria table (say) enter the functions to G1 and below:
=DMAX($A$1:$C$n,1,$E$1:$F2)
where n is the last row in your source table.

 

[Salut39]

It looks like it brings me just max for the whole column not for each month.?

 

[KenWright]

If you simply select your list of dates, throw a pivot table at it, drag Date into ROW fields and group on 'Months and year' and then also drag Date into the DATA field.

Right click on any of the values you see and then choose field settings, and then where it says summarise by, choose max.

Finally, right click on any of the values you see, choose field settings, choose number and then pick a date format.

Done.

Took me a lot longer to write that than to do it.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Salut39]

Thanks Ken.

 

[Rofeu]

Hi,

I must be missing something here.

How do you group on 'Months and Year' in a pivot table?

Cheers,

Roel

 

[xlbo]

right click on the date field and choose "Group by"

If you have valid dates, excel will recognise this and give you month / year etc options

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

CAVEAT for Group by DATE in a PivotTable...

1. You must have REAL DATES

2. ALL rows must have a valid date (no blanks or text)

Skip,

_[red][/red]

 

[Rofeu]

ah, now I've got it. Saw that, but was thinking it would make it into one of those dropdown groups as it would with normal rows/columns.

Thanks, great to learn something new!

Cheers,

Roel