Microsoft KB Archive/118372

= Transpose Function Returns Date Value as Text =

Article ID: 118372

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q118372



SYMPTOMS
In Microsoft Excel, when you use a date value in the Transpose function in a Visual Basic procedure, the date value is returned to your worksheet as a text string, such as 1/1/94, that cannot be formatted with a date format.



CAUSE
This behavior occurs when you do not explicitly dimension your date value as a variable of either Single or Double data type. Note that this behavior does occur when you dimension your date value as Date type.



WORKAROUND
To avoid having the Transpose function return a date value as a text value, define any date that you use in this function as a variable of Single or Double data type as in the following example:

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400. Function Return_Date ' dimension variable my_date array to     ' contain double date type values Dim My_Date(1) As Double My_Date(0) = DateSerial(1994, 1, 1) My_Date(1) = DateSerial(1994, 1, 2) Return_Date = Application.Transpose(My_Date) End Function

