formula in excel

[Guest]

I have some dates in a column like

1/01/2006
10/01/2006

I want just the month and year to be there but I cant just format the cell
because I need to extract to a pivot table and it still picks up the day

I tried to combine the formulas for MONTH and YEAR but no luck

Any ideas

 

[joeu2004]

I have some dates in a column like
1/01/2006
10/01/2006
I want just the month and year to be there but I cant just format the cell
because I need to extract to a pivot table and it still picks up the day

Did you try this: Format > Cells > Number > Custom > m/yyyy?

I tried to combine the formulas for MONTH and YEAR but no luck

Did you try this:

=month(A1) & "/" & year(A1)

This results in text, not a number. Not sure it helps.

 

[Guest]

Create a seperate column for month (and/ or) year

paste this function
=month(A1) ' --- in month column
=year(A1) ' --- in year column


*** please do rate ***

 

[Gloria Thrurk]

If you want it in a pivot table it's best to keep the date in long form such
as
27/01/2001
1/6/2002

and in the pivot table, use the Group By feature to group the date by year,
quarter, month ...etc.

Gloria

 

[Gloria Thrurk]

If you take out the day the pivot table will not recognise the date as a
date.
leave the date in and use the Group by on that cell.
It's easy peasy.
HTH
G.