Date as month in Pivot

[student]

Hi,

I have a list of sites and their Installation Dates
some what like this

Site id Ins. Date
MUM_001 3-FEB-04
MUM_002 5-FEB-04
MUM_013 13-Mar-04
MUM_016 7-Apr-04
DEL_001 14-FEB-04
.. .
.. .
.. .
.. .

Now i want to group by month in Pivot. Some thing like this:

Month Total site (Count of sites)

Feb 04 2
Mar 04 1
Apr 04 1
FEB 05 1

is this possible ??

Thanks

 

[Max]

Perhaps one alternative way to try ..

Assuming in Sheet1 is the table below
in cols A and B, data from row2 down:

Site id Ins. Date
MUM_001 3-FEB-04
MUM_002 5-FEB-04
MUM_013 13-Mar-04
MUM_016 7-Apr-04
DEL_001 14-FEB-05 [note: typo corrected]

In Sheet2 is the table below in cols A and B,
with dates in col A ("mmm-yy" formatted)
from row2 down:

Month Total site (Count of sites)
Feb 04
Mar 04
Apr 04
Feb 05

Put in B2:

=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$10,"mmm-yy")=TEXT(A2,"mmm-yy"))+0)

Copy B2 down. Col B will return the desired results.

Adjust the range "Sheet1!$B$2:$B$10" to suit

 

[student]

Thanks for ur reply Max. But it gives me #NUM!.

Perhaps one alternative way to try ..

Assuming in Sheet1 is the table below
in cols A and B, data from row2 down:

Site id Ins. Date
MUM_001 3-FEB-04
MUM_002 5-FEB-04
MUM_013 13-Mar-04
MUM_016 7-Apr-04
DEL_001 14-FEB-05 [note: typo corrected]

In Sheet2 is the table below in cols A and B,
with dates in col A ("mmm-yy" formatted)
from row2 down:

Month Total site (Count of sites)
Feb 04
Mar 04
Apr 04
Feb 05

Put in B2:

=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$10,"mmm-yy")=TEXT(A2,"mmm-yy"))+0)

Copy B2 down. Col B will return the desired results.

Adjust the range "Sheet1!$B$2:$B$10" to suit
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hi,

I have a list of sites and their Installation Dates
some what like this

Site id Ins. Date
MUM_001 3-FEB-04
MUM_002 5-FEB-04
MUM_013 13-Mar-04
MUM_016 7-Apr-04
DEL_001 14-FEB-04
. .
. .
. .
. .

Now i want to group by month in Pivot. Some thing like this:

Month Total site (Count of sites)

Feb 04 2
Mar 04 1
Apr 04 1
FEB 05 1

is this possible ??

Thanks

 

[student]

Max,

i added 1 col in my sheet populated by TEXT(Sheet1!$B$2:$B$10,"mmm-yy")
as a result i had

Now i can run Pivot on this data. Thanks very much MAX.

Thanks for ur reply Max. But it gives me #NUM!.

Perhaps one alternative way to try ..

Assuming in Sheet1 is the table below
in cols A and B, data from row2 down:

Site id Ins. Date
MUM_001 3-FEB-04
MUM_002 5-FEB-04
MUM_013 13-Mar-04
MUM_016 7-Apr-04
DEL_001 14-FEB-05 [note: typo corrected]

In Sheet2 is the table below in cols A and B,
with dates in col A ("mmm-yy" formatted)
from row2 down:

Month Total site (Count of sites)
Feb 04
Mar 04
Apr 04
Feb 05

Put in B2:

=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$10,"mmm-yy")=TEXT(A2,"mmm-yy"))+0)

Copy B2 down. Col B will return the desired results.

Adjust the range "Sheet1!$B$2:$B$10" to suit
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hi,

I have a list of sites and their Installation Dates
some what like this

Site id Ins. Date
MUM_001 3-FEB-04
MUM_002 5-FEB-04
MUM_013 13-Mar-04
MUM_016 7-Apr-04
DEL_001 14-FEB-04
. .
. .
. .
. .

Now i want to group by month in Pivot. Some thing like this:

Month Total site (Count of sites)

Feb 04 2
Mar 04 1
Apr 04 1
FEB 05 1

is this possible ??

Thanks

 

[Max]

Glad to hear you got it working !
Thanks for the feedback