为什么当1被传递给它时，日期返回“31-12-1899”?

I am using windows 10 OS excel 13 so in the below code 1 should return 1/1/1900 right ? below it doesn't why.

我正在使用windows 10操作系统excel 13，所以在下面的代码1中应该返回1/1/1900，对吧?下面没有为什么。

On this question OP passed 2016 and got the same result I got so if there is some error why do I got the same result as OP when I passed 2016 ?

在这个问题上，OP通过了2016年，得到了同样的结果，如果有一些错误，为什么我在2016年的时候得到了与OP相同的结果?

Sub ndat()

Dim dt As Date
dt = 2016
Debug.Print "dt is " & dt

End Sub

2 个解决方案

#1

Integers and dates map differently in VBA than in the worksheet. For example:

VBA中的整数和日期映射与工作表不同。例如:

Sub marine()
    Dim i As Integer, d As Date
    Dim mgs As String

    msg = ""
    For i = -10 To 10
        d = CDate(i)
        msg = msg & i & vbTab & Format(d, "mm/dd/yyyy") & vbCrLf
    Next i

    MsgBox msg
End Sub

Produces:

生产:

Note you can get dates prior to 1/1/1900

注意，你可以在1/1900之前得到日期。

EDIT#1:

编辑# 1:

This may help to understand the difference. I put some integers in column A.

这可能有助于理解差异。我把一些整数写在A列。

In B1, I put =A1 and copy down. I format column B to display in date format.

在B1中，我写上=A1，然后复制下来。我格式化列B以显示日期格式。

I use this UDF():

我用这个UDF():

Public Function VBA_Date(i As Long) As String
    VBA_Date = Format(CDate(i), "mm/dd/yyyy")
End Function

To fill column C:

C:填列

Note the transition between rows #19 and #20

注意第19行和第20行之间的转换。

#2

It is just the worksheet itself. Or to be correct: The worksheet-functionality!

它只是工作表本身。或者是正确的:工作功能!

Quick test:

快速测试:

?cdate(1)
1899-12-31 
?format(1,"YYYY-MM-DD")
1899-12-31
?worksheetfunction.Text(1,"YYYY-MM-DD")
1900-01-01

But going for todays date does not show this gap:

但是今天的约会并没有显示出这个差距:

?clng(now)
 42463 
?worksheetfunction.Text(now,"0")
42463

That shows that somewhere between 1 and 42463 is a gap (the quick lotus check shows it:

这表明在1到42463之间有一个间隙(快速lotus check显示:

?cdate(60) & " --- " & cdate(61)
1900-02-28 --- 1900-03-01
?format(60,"YYYY-MM-DD") & " --- " & format(61,"YYYY-MM-DD")
1900-02-28 --- 1900-03-01
?worksheetfunction.Text(60,"YYYY-MM-DD") & " --- " & worksheetfunction.Text(61,"YYYY-MM-DD")
1900-02-29 --- 1900-03-01

Just one last test to show it again:

最后再做一个测试:

?format("1900-02-28","0") & " --- " & format("1900-03-01","0")
60 --- 61
?worksheetfunction.Text("1900-02-28","0") & " --- " & worksheetfunction.Text("1900-03-01","0")
59 --- 61

starting with 61, there is simply now difference in numbers. But the Lotus-Bug adds the 1900-02-29 for "compatibility".

从61开始，现在的数字有了明显的差异。但是lotu - bug添加了1900-02-29作为“兼容性”。

Also: that is a "feature" for excel and has nothing to do with basic, vbscript, vba.......... All other programs work in the correct way and it would get lots of trouble if VBA would not. So for "compatibility" the VBA in excel just does it in the correct way ;)

同样:这是excel的一个“特性”，与basic、vbscript、vba……毫无关系所有其他程序都以正确的方式工作，如果VBA不这样做的话，将会有很多麻烦。对于“兼容性”，excel中的VBA只是以正确的方式实现;

#1