Summarizing Dates into Months and Years
 

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

Summarizing Dates into Months and Years
 

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

regards
FSt1

"Yuanhang" wrote:

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

Summarizing Dates into Months and Years
 

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.

"FSt1" wrote:

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

regards
FSt1

"Yuanhang" wrote:

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

Summarizing Dates into Months and Years
 

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




"Yuanhang" wrote in message
...
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

Summarizing Dates into Months and Years
 

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

sorry
FSt1

"FSt1" wrote:

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

regards
FSt1

"Yuanhang" wrote:

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

Summarizing Dates into Months and Years
 

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.
--
David Biddulph

"FSt1" wrote in message
...
correct that formula
=text(A1)&", "&Year(A1)


sorry
FSt1

"FSt1" wrote:

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

regards
FSt1

"Yuanhang" wrote:

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

Summarizing Dates into Months and Years
 

yes. correct it again. bad day.
thanks.

"David Biddulph" wrote:

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.
--
David Biddulph

"FSt1" wrote in message
...
correct that formula
=text(A1)&", "&Year(A1)


sorry
FSt1

"FSt1" wrote:

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

regards
FSt1

"Yuanhang" wrote:

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

Summarizing Dates into Months and Years
 

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 $6C2),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)


--
Biff
Microsoft Excel MVP


"Yuanhang" wrote in message
...
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

Summarizing Dates into Months and Years
 

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$6C2),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 $6B2),A$2:A$6)))


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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 $6C2),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)


--
Biff
Microsoft Excel MVP


"Yuanhang" wrote in message
...
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