Summarizing Dates into Months and Years

[Yuanhang]

Hey, assume I have column A like that as followed:

Column A
12/11/07
1/07/08
2/13/08
2/13/08
3/11/08

Basically, column A are some dates sorted ascendingly. They could be in
different years or they might be at the same date sometimes.

So, is it possible to let Excel create a new column by picking up different
months and years but ignore the dates. Basded on comlumn A above, if
possible, column B gonna look like:

Column B
Dec., 2007
Jan., 2008
Feb., 2008
March., 2008

 

[FSt1]

hi
you could use this formula in column B...
=TEXT(MONTH(A1),"mmm")&", "&YEAR(A1)

regards
FSt1

 

[Yuanhang]

Thank you for quick response. But your way seems gonna show same months and
years in different rows which is not that perfect.
Let's say, if there're ten dates with Feb., 2008, I would like all those
dates be summerized into only one cell reading "Feb., 2008".
Thank you again.

 

[Conan Kelly]

Yuanhang,

You could just set column B to be the same date in each month & year.

If data starts in row 2, enter this formula in cell B2:

=date(Year(a2),month(a2),1)

Then copy that formula down as necessary. Format all cells with this
formula as "mmm yyyy".

HTH,

Conan

 

[FSt1]

correct that formula
=text(A1)&", "&Year(A1)

sorry
FSt1

 

[David Biddulph]

Do you want to correct that again?
Perhaps not =text(A1)&", "&Year(A1) but =text(A1,"mmm")&", "&Year(A1) ?

But although that gives the format, of course it doesn't address the OP's
wish to list just one entry for each month.

 

[FSt1]

yes. correct it again. bad day.
thanks.

Do you want to correct that again?
Perhaps not =text(A1)&", "&Year(A1) but =text(A1,"mmm")&", "&Year(A1) ?

But although that gives the format, of course it doesn't address the OP's
wish to list just one entry for each month.

 

[T. Valko]

Try this:

Dates in A2:A6

Enter this formula in B2:

=IF(COUNT(A2:A6),MIN(A2:A6),"")

Format as CUSTOM: mmm, yyyy

Enter this array formula** in B3 and copy down until you get blanks:

=IF(OR(C2="",C2=MAX(A$2:A$6)),"",MIN(IF((MONTH(A$2:A$6)<>MONTH(C2))*(YEAR(A$2:A$6)>=YEAR(C2))*(A$2:A$6>C2),A$2:A$6)))

Format as CUSTOM: mmm, yyyy

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

Ooops!

I have the references all messesd up!!!!!!

Enter this array formula** in B3 and copy down until you get blanks:
=IF(OR(C2="",C2=MAX(A$2:A$6)),"",MIN(IF((MONTH(A$2:A$6)<>MONTH(C2))*(YEAR(A$2:A$6)>=YEAR(C2))*(A$2:A$6>C2),A$2:A$6)))

The correct formula entered in B3 should be:

=IF(OR(B2="",B2=MAX(A$2:A$6)),"",MIN(IF((MONTH(A$2:A$6)<>MONTH(B2))*(YEAR(A$2:A$6)>=YEAR(B2))*(A$2:A$6>B2),A$2:A$6)))