Extracting the Month value from a fully qualified date column

[Brian]

Hi,

I am trying to extract the month value from a date column (clsddate)
that is refreshed from a database on open. This is to create a pivot
table report (seprate sheet) that specifies number of WO_NUM by month.
(using count function in the pivottable for total of WO_NUM)

WO_NUM CLSDDATE
19330 11/03/2004 10:46
20704 10/03/2004 09:43
22978 17/03/2004 16:09
23530 23/03/2004 10:10
23653 13/04/2004 15:29
24263 05/01/2004 14:28
24514 05/01/2004 10:58
25952 13/01/2004 14:28
26079 05/01/2004 11:17
26117 12/03/2004 14:33
26311 11/02/2004 11:17
26325 16/02/2004 16:13
26349 26/01/2004 07:51
26367 17/03/2004 17:26
26455 14/01/2004 11:11
26478 08/01/2004 10:36

Any ideas would be appreciated

Thanks in advance
Brian

 

[Jerry W. Lewis]

See Help for the MONTH() worksheet function.

Jerry

 

[Alex Delamain]

Adding a helper column simplifies matters
If the dates start in B1 then add a formula such as the one below

=100*right(year(b1),1)+month(b1)

this produces a number such as 409 (September 2004) which can b
summarised in a pivot table. If you don't need to roll round from yea
to year then just =month(b1) is enoug

 

[rank Kabel]

Hi
then I would use
=TEXT(A1,"YMM")

 

[Dave Peterson]

If I were going to do it this way, maybe another helper column of cells:

=text(b1,"yyyy-mm")

(I like 4 digit years.)

But if the field actually contains dates, you can group within the pivottable
itself.
Inside the Pivottable
rightclick on the Date Header
select Group and show detail
Then Group
then months (or months and years???)

 

[Brian]

Hi Guys,

Thanks for the quick response, after trying the methods here I am
going with a combination of all of them with some VBA code to refresh
the pivottable on open and refresh the helper column.

Thanks
Brian