为什么将具有dd/mm/yy格式的日期的数组转置将某些日期更改为mm/dd/yy格式? [英] Why does transposing array with dates in format dd/mm/yy change some dates to mm/dd/yy format?

问题描述

当我转置包含日期的一维数组时，为了将它们完整地打印到一张纸上，某些日期将从dd/mm/yy更改为mm/dd/yyyy.

When I transpose a 1 dimensional array, containing dates, in order to print them to a sheet in it's entirety, some dates are changed from the dd/mm/yy to mm/dd/yyyy.

尤其是当月的某天:

• 小于或等于12，例如January 2, 2016 (02/01/16)或May 11, 2016 (11/05/16)，则日期以日期格式mm/dd/yy打印并右对齐.
• 大于或等于13，例如April 23, 2016 (23/04/16)或December 17, 2016 (17/12/16)，则日期以日期格式dd/mm/yyyy打印并向左对齐.

• is less than or equal to 12, such as January 2, 2016 (02/01/16), or May 11, 2016 (11/05/16), then the date is printed with the date format mm/dd/yy and is aligned right.
• is greater than or equal to 13, such as April 23, 2016 (23/04/16), or December 17, 2016 (17/12/16), then the date is printed with the date format dd/mm/yyyy and is aligned left.

当我使用for循环分别打印每个日期时，或者我不转置数组并在每个列的第一行中打印每个日期时，所有日期都以dd/mm/yy格式打印并且所有日期都对齐是的.

When I use a for loop to print each date separately, or I do not transpose the array and print each date in the first row of each column however, all dates are printed with the format dd/mm/yy and all dates are aligned right.

我有:

• Windows 8.1(美国英语)
• Office 365学生(美国英语)(Excel 2016 32位)
• 语言环境设置:荷兰

Option Explicit

Sub TransposeDatesArray()
    Dim arrDates() As Date
    Dim i As Variant

    ReDim arrDates(0)

    For i = CDate("Januari 01, 2016") To CDate("December 31, 2016")
        If UBound(arrDates) = 0 Then
            ReDim arrDates(1 To 1)
        Else
            ReDim Preserve arrDates(1 To UBound(arrDates) + 1)
        End If
        arrDates(UBound(arrDates)) = i
    Next

    With ThisWorkbook.Worksheets(1)
        .Cells.Delete
        .Cells(1, 1).Resize(UBound(arrDates)).Value = Application.Transpose(arrDates)
        .Cells(1, 2).Resize(UBound(arrDates)).Value2 = Application.Transpose(arrDates)
        .Cells(1, 3).Resize(UBound(arrDates)).Formula = Application.Transpose(arrDates)

        For i = LBound(arrDates) To UBound(arrDates)
            .Cells(i, 4).Value = arrDates(i)
            .Cells(i, 5).Value2 = arrDates(i)
            .Cells(i, 6).Formula = arrDates(i)
        Next

    End With
End Sub

结果:

推荐答案

进行了更多研究之后，我发现了以下内容:

After doing some more research, I have found the following:

Application.Transpose(arrDate)似乎不仅会转置数组，而且还会将日期值存储为实际日期时进行转置.

It would seem that the Application.Transpose(arrDate) transposes not only the array, but also date values when they are stored as actual date.

考虑日期42373(2016年1月4日)

Consider the date , 42373 (January 4, 2016)

• Debug.Print Format(CDate(42373), "mmmm d, yyyy")
  • 产生januari 4, 2016
  • Debug.Print Format(CDate(42373), "mmmm d, yyyy")
    • produces januari 4, 2016
    • 产生april 1, 2016

    当存储为实际日期时，似乎可以转换日期值.换位有效地将日期从day/month重新排序为month/day，之后该月成为一天，而该天成为月份，因为系统仍使用day/month格式.仅当月份中的天数小于等于12时才能执行此操作，因为移置后就变成了月份.

    It appears that a date value can be transposed when stored as an actual date. The transposing effectively reorders the date from day/month to month/day after which the the month becomes the day and the day becomes the month because the system still uses the day/month format. This can only be done if the day of the month is 12 or less, because after transposing the day becomes the month.

    这篇关于为什么将具有dd/mm/yy格式的日期的数组转置将某些日期更改为mm/dd/yy格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！