Coverting A Number toText

[Kdoyle5]

I maybe going about this all wrong, but I'm a sales professional and need to summarize backlog data by month and year.
The base report has the ship date by month, day and year from which I am able to extract the just the month and year into separate columns. From here I generate a pivot table, selecting the year and month collumns. The table that this generates sums the month and year because theyre in General format. The way I'm getting around this now is to make an edit of the month and year adding a (') at the beginning of each so that Excel handles it as text.

I thought that using the text() function would fix that issue but I don't understand what to enter into the formula for format ( text(cell, format).

Am I on the correct track or is there another function I should be using? If I am correct, what is the input to the 2nd variable?

[Richard Buttrey]

Hi,

It might be better if you upload your workbook so that we can see your request in context. Generally it's preferable to keep dates as proper Excel date numbers so that you can use them in calculations and summaries. It's not clear to me why you are wanting to keep the values as text. I suspect you'd be better advised to construct a proper date number rather than work with text.

Regards

[Miraun]

The issues are lying within the configuration of the pivot tables, and not the formatting of the information. Swap the months over to the column section of the pivot table to organize and count/summarize by the month numbers that you're using. It sounds right now that you have it within the Data fields.

It will also defaultly put a subtotal in there, but you can toggle that with options around Pivot Tables.

I feel that the UI for Pivot tables was much better in 2003 relative to 2007, so unfortunately, I've had some problems myself getting everything arranged the way I want it. My advice is really just to play with it, and without seeing the data, it'd be difficult to just kind of take care of it for you.