Dates not recognized when downloaded from Peoplesoft
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.
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.
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
=MONTH(DATEVALUE(F2))
That will give the number of the month.
Or just use this:
DATEVALUE(F2)
and format the cell as mmm