Date to change format

[rexmann]

Hi

I have list of data with various dates.

Example 06/01/08 item1
07/01/08 item2

What I want is another column which displays just the month and year.
However it is going to be used in a pivot table so the actual day information
needs to be lost so I can filter on it (if I change the format to month and
year info is hidden but still there so displays as many dates in the pivot
table).

Hope this makes sense, in short I need to remove the day information
completely and have just the date and year.

Any greatly appreciated

Kind regards

Rexmann

 

[Joel]

The cell(s) with the Month Year needs to be formated at text not general
otherwise it will revert back to a Real Date (but that really doesn't make a
difference) it should still work in a Pivot Table). You really just need to
convert each date to the same day of the Month like the first Day of each
month .

any way try this

=Month(A1)&Year(A1)

or

=Month(A1)&" "&Year(A1)

or

=Month(A1)&" - "&Year(A1)

 

[Stefi]

Try this formula:
=TEXT(A1,"yyyy.mm")

Regards,
Stefi

„rexmann†ezt írta:

 

[Dave Peterson]

If you're only going to use it in a pivottable, you don't need that helper
column.

If you want use autofilter on the raw data, then ignore this.

Rightclick on any of those dates in the column area (after you finish the
pivottable).

Select Group and show detail
Choose Months and Years

If you have any text or empty cells in that date field, excel won't let you
group your data.