Data Field in Pivot Table

[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?

 

[Tom Ogilvy]

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

 

[Dave Peterson]

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

 

[John Pierce]

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.