XL5: Transpose Function Returns Date Value as Text

• Microsoft Excel for Windows, version 5.0

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 engineers 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

REFERENCES

For more information about the TRANSPOSE function, choose the Search button in Help and type:

   TRANSPOSE