formula in excel

M

max power

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
 
J

joeu2004

max said:
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
Click to expand...

Did you try this: Format > Cells > Number > Custom > m/yyyy?
I tried to combine the formulas for MONTH and YEAR but no luck
Click to expand...

Did you try this:

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

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

Naveen

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 ***
 
G

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
 
G

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.
 
Top