Summarizing Dates into Months and Years

  • Thread starter Thread starter Yuanhang
  • Start date Start date
Y

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
 
hi
you could use this formula in column B...
=TEXT(MONTH(A1),"mmm")&", "&YEAR(A1)

regards
FSt1
 
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.
 
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
 
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.
 
yes. correct it again. bad day.
thanks.

David Biddulph said:
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.
Click to expand...
 
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)
 
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)))
Click to expand...

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)))
 
Back
Top