Bizarre Problem! How to Change the format of a cell to be used in Pivot Table

[yoyoma123]

Hi All,

I have run into a rather bizarre problem suddenly, and I can't see to find the right solution for it anywhere.

I have a table with several calculated fields, one of which is called "Quarter Month," which describes where in the quarter that month falls (1,2 or 3).

It is a Vlookup function that looks up the month in the Date, see which quarter month that corresponds to my reference array, and returns either 1,2 or 3.

Here is the formula that I currently have:

=VLOOKUP(MONTH(B2),Stats!$F$2:$G$13,2,FALSE)

However, this formula only returns the response in date form, 1-Jan, 2-Jan, 3-Jan, and I have to switch it either text or general format, which is fine, but it also messes up my pivot tables.

Even if I format the column in the source table as a Text Format, the data shows up as Dates in the pivot table. It's especially problematic because I have a Macro that changes the pivot table's filter that requires the user to input the Qtr Month (1,2, or 3), but these inputs do not match the values in field, since the "actual" value is 1-Jan, etc!

What is going on? Does anyone have a solution?

 

[Gerald Higgins]

I can't replicate this.
I used your formula and the values returned were 1, 2, 3 and so on.
I also threw them into a pivot table, and they came through fine.

Instead of formating them as text or general, try formating them as NUMBER.
OR, try using text values in your lookup table, instead of numbers.