Dates not recognized when downloaded from Peoplesoft

Davisron867
Davisron867 used Ask the Experts™
on
I'm experiencing some unexpected behavior in a PeopleSoft nVision matrix report.

The report layout has a date code in Row 2, which is hidden

-Cell F2 has the PeopleSoft code %ASD% to return the Report Date, so for Apr 30, 2011,
the formula returns '2011-04-30 (including the apostrophe).

-Cell F10 is a column header with the formula =Text(F2,"mmm") and should display Apr

Both cells are formatted as General.

The result of the text formula in cell F10 has always been Apr,but when we run the same layout in a new production environment residing offshore, the result in cell F10 is the number 4 instead of Apr.

The column header in cell F10 will magically update and resolve to "Apr" if you
-expand the hidden row (row 1) with the %ASD% formula, or
-activate either cells F1 or F10.

But, we dont want to have to manually intervene to get the header to display the month as Apr. I could use VBA to activate cells in the This Workbook object, but I'd like to understand why this is happening and if there's a non-VBA solution.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Instead of TEXT, try this:

=MONTH(DATEVALUE(F2))

That will give the number of the month.  

Or just use this:

DATEVALUE(F2)

and format the cell as mmm
Commented:
or maybe this is what you want?

=TEXT(DATEVALUE(E1),"mmm")

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial