Data Field in Pivot Table

  • Thread starter Thread starter John Pierce
  • Start date Start date
J

John Pierce

Hello Experts,
I enter a date on a form, e.g., "04/09/06", and if I step
through the code, and click on the variable StartDate,
I get the little box that says StartDate = "04/09/06".
Great. Then I write the data to a sheet, like this,
..Offset(0, 2).Value = StartDate
..Offset(0, 3).Value = Format(StartDate, "mm/yy")

On the spreadsheet they look like this: 04/09/06 04/06
But in the Formula Bar they look this: 4/9/2006 4/6/2006


I am using the column with the mm/yy as a Row in a
Pivot Table. The Pivot Table works fine, but the labels
don't make sense and I can't get them to change.
Down the left side of the Table is a column with
4/6/2006
4/6/2006 Total
5/6/2006
5/6/2006 Total
etc.

The PivotTable is using what shows in the Formula Bar.
Why is that different from what is in the Cells?
 
What is in the formula bar is what is in the cell.

Just like if you put

5.23753467
in a cell and formatted it to show 2 decimal places. The cell still
contains the original number even though it displays as 5.24
 
Using this line:
..Offset(0, 3).Value = Format(StartDate, "mm/yy")
is the equivalent of typing 04/06 in the cell.

Excel will see it as m/d (or d/m depending on your windows setting) of the
current year.

If you want to see the text 04/06 in the cell, you could use:

..Offset(0, 3).Value = "'" & Format(StartDate, "mm/yy")

or
with .Offset(0, 3)
.numberformat = "@" 'text
.Value = Format(StartDate, "mm/yy")
end with

But I'm guessing that you want a real date in that cell, but want to see the
month/year.

I'd use:
with .Offset(0, 3)
.numberformat = "mm/yy"
.Value = StartDate
end with

Or if you want the first of that month:

with .Offset(0, 3)
.numberformat = "mm/yy"
.Value = dateserial(year(StartDate), month(startdate), 1)
end with
 
Thanks Tom, Thanks Dave, for the explanation and code.
I tried .Offset(0, 3).Value = "'" & Format(StartDate, "mm/yy")
and it works perfectly for me. Thanks again.
 
Back
Top