Calling Excel Wizards..
#1
How do I do this???
For eg.
I currently have dates displayed as 26-Jul-02
When I format cells to show as Jul-02 it appears fine.
However when I use the data in a pivot table it shows as 26-Jul-02 again.
I understand how the date function works. But I cannot find out how to get excel to accept that I only want month
and year info in my pivot table.
Does any one know what I need to do.
All help greatly appreciated.
Nick
For eg.
I currently have dates displayed as 26-Jul-02
When I format cells to show as Jul-02 it appears fine.
However when I use the data in a pivot table it shows as 26-Jul-02 again.
I understand how the date function works. But I cannot find out how to get excel to accept that I only want month
and year info in my pivot table.
Does any one know what I need to do.
All help greatly appreciated.
Nick
#3
That works sort of.
But unfortunatley I end up with lots of columns in the pivot table for march july etc. I only want one of each month. That is why I was trying to remove the day component.
I just want excel to ignore the day and not the month or year. When you format for mmm-yy it appears to have done it. But it is still remembering the day secretly this ***** up my pivot table and gives me hundreds of columns instead of 12.
But unfortunatley I end up with lots of columns in the pivot table for march july etc. I only want one of each month. That is why I was trying to remove the day component.
I just want excel to ignore the day and not the month or year. When you format for mmm-yy it appears to have done it. But it is still remembering the day secretly this ***** up my pivot table and gives me hundreds of columns instead of 12.
#4
Ok, firstly, chnaging how something looks (format) doesn't mean it is no longer there.
So changing the format won't help, besides the way dates are used in excel if you just used mmm-yy it would still have a day as 01.
Why not simply have another column in the table with the month and use this column in the pivot table?
So changing the format won't help, besides the way dates are used in excel if you just used mmm-yy it would still have a day as 01.
Why not simply have another column in the table with the month and use this column in the pivot table?
#5
oh just in case you don't know how to do it...
=MONTH(cell with date in it)
That will return the number of the month
then use this formular in the next column to change that to a month in words
=INDEX($G$7:$H$18,E2,2)
G7:H18 is a table of 1-12 on the left and Jan to Dec on the right and E2 is the cell with the result from MONTH
=MONTH(cell with date in it)
That will return the number of the month
then use this formular in the next column to change that to a month in words
=INDEX($G$7:$H$18,E2,2)
G7:H18 is a table of 1-12 on the left and Jan to Dec on the right and E2 is the cell with the result from MONTH
#6
OK,
Create a field with the following =Month(Cell with date in it)&"/"&Year(Cell with date in it). Then pivot on that.
Or you can use groups in your pivot table I think...
1 Select a single date or time item in the field you want to group.
2 On the PivotTable toolbar, click Group .
3 If necessary, type the first item to group in the Starting at box, and type the last item to group in the Ending at box.
4 In the By box, click one or more time periods for the items in the group.
If you clicked only Days in the By box, enter the size of the group in the Number of days box. For example, type 7 in the Number of days box to group items by weeks.
Not sure if you can split by month and year though.
Cheers,
Posh
Create a field with the following =Month(Cell with date in it)&"/"&Year(Cell with date in it). Then pivot on that.
Or you can use groups in your pivot table I think...
1 Select a single date or time item in the field you want to group.
2 On the PivotTable toolbar, click Group .
3 If necessary, type the first item to group in the Starting at box, and type the last item to group in the Ending at box.
4 In the By box, click one or more time periods for the items in the group.
If you clicked only Days in the By box, enter the size of the group in the Number of days box. For example, type 7 in the Number of days box to group items by weeks.
Not sure if you can split by month and year though.
Cheers,
Posh
Trending Topics
#8
Edited to say I realised I had tried something similiar to that but I need it to have the month and year on.
As the pivot table runs over a couple of years.
If there was an =MONTHANDYEAR(D3)command that would do but there doesnt seem to be such a thing.
Thanks for trying.
Nick
[Edited by Nikko2 - 4/5/2003 10:23:12 AM]
As the pivot table runs over a couple of years.
If there was an =MONTHANDYEAR(D3)command that would do but there doesnt seem to be such a thing.
Thanks for trying.
Nick
[Edited by Nikko2 - 4/5/2003 10:23:12 AM]
Thread
Thread Starter
Forum
Replies
Last Post
Mattybr5@MB Developments
Full Cars Breaking For Spares
28
28 December 2015 11:07 PM
Mattybr5@MB Developments
Full Cars Breaking For Spares
12
18 November 2015 07:03 AM