Summarizing Dates into Months and Years


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
 
Ad

Advertisements

F

FSt1

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

regards
FSt1
 
Y

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.
 
C

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
 
D

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.
 
Ad

Advertisements

F

FSt1

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.
 
T

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)
 
Ad

Advertisements

T

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top