Returning a "Real" month

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

When you have a column of dates, is it possible to use the
Month function to return a column of just the Month as
a "real" field, not just a "label?"

For example, when you use the month function
on '12/16/03', you return '12' or 'Dec' depending on how
the destination column is formatted. However, the
destination is really still '12/16/03.'

I want to be able to subtotal on the month of a list of
dates, and use the month in a pivot table. Any ideas?
 
Why not just use an extra column and a formula

=MONTH(A2)

copy down

when you do the pivot table drag the numeric values to Data and the months
to the Row
also you can subtotal for each change in month although the picot tables
will do just that
if you do the above
 
Michael

The best way is to use a helper column alongside your data, with =MONTH(A2)
in it. Format this as mm, or mmm, and then select the column, Copy and
Paste/Special/Values.

Andy.
 
To keep the value a real date, I just use the first of the month to represent
that month:

if A2 contained the date, then my helper cell would contain:

=date(year(a2),month(a2),1)

then I could format it to make it look nice and I could use the grouping (by
month and year) within the pivottable.
 
Thanks!

This is great.
-----Original Message-----
To keep the value a real date, I just use the first of the month to represent
that month:

if A2 contained the date, then my helper cell would contain:

=date(year(a2),month(a2),1)

then I could format it to make it look nice and I could use the grouping (by
month and year) within the pivottable.




--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
Back
Top