VBA没有使用Now()返回正确的日期 [英] VBA doesn't return the correct Date with Now()
问题描述
我有一个简单的代码,VBA无法返回正确的日期:
I have a simple code and VBA cannot return the correct date:
Sub Test_Date ()
Debug.Print Format(Month(Now()), "mmm")
Debug.Print Month(Now())
Debug.Print Now()
End Sub
返回:
Dez
1
29.01.2018 11:17:55
1)这是Excel众所周知的问题,还是我做错了?
1) Is that an Excel well known issue or did I make a mistake?
推荐答案
这是一个已知问题。
在 Debug.Print Format(Month(Now()), mmm)
您正在传递
Debug.Print Format(1, mmm)
。
在VBA中的第一天是 31.12.1899
。它的月份是十二月
。运行这段小代码以查看它:
And the first day in VBA is 31.12.1899
. And its month is December
. Run this small piece of code to see it:
Sub TestMe()
Debug.Print Format(1, "dd-mmm-yyyy")
End Sub
如果要当前日期的月份- Format(Now, mmm)
通常,在Excel,VBA和MSSQL Server中处理日期时要格外小心。日期将转换为数字。例如,每个日期都会转换为数字,但是起始数字可能会有所不同。
In general, be a bit careful, when you are working with dates in Excel, VBA and MSSQL Server. The dates are converted to numbers. E.g., every date is converted to a number, but the starting number can be a bit different.
- 在Excel中,
1
转换为01.January.1900
; - 在VBA中,
1
转换为1899年12月31日
; - 在MSSQL Server中,
1
转换为02.January.1900
(SELECT CONVERT(DATETIME,1)
); - 在Excel中,如果激活
ActiveWorkbook.Date1904 = True
属性,则1
转换为02.January.1904
;
- In Excel, the
1
is converted to01.January.1900
; - In VBA, the
1
is converted to31.December.1899
; - In MSSQL Server, the
1
is converted to02.January.1900
(SELECT CONVERT(DATETIME,1)
); - In Excel, if you activate the
ActiveWorkbook.Date1904=True
property, the1
is converted to02.January.1904
;
原因是Lotus 1-2-3的创建者犯了一个错误,他们认为29.02.1900是有效日期。因此,Excel希望与Lotus 1-2-3兼容,并且他们继续执行此错误。在VBA中,他们决定不实施该错误,因此VBA和Excel中的日期略有不同,但这仅适用于直到1日的时期。1900年3月- MSDN日期说明。
The reason for this is an error, made by the creators of Lotus 1-2-3, who have thought that 29.02.1900 was a valid date. Thus, Excel wanted to become compatible with Lotus 1-2-3 and they have carried on with this error. In VBA, they have decided not to implement the error, thus the dates in VBA and Excel are a bit different, but this is only for the period up to 1. March 1900 - MSDN date explanation.
因此,根据您正在使用的上方四个环境中的哪个环境,今天的日期(2018年1月29日)可以转换为下列值之一:
Thus, depending on which one of the 4 "environments" above you are working, today's date (29-January-2018) can be converted to one of the following:
- 41667(Excel中带有Date1904)
- 43128(MSSQL Server
SELECT CONVERT( INT,CONVERT(DATETIME,GETDATE()))
) - 43129(Excel)
- 43129(VBA)
- 41667 (Excel with Date1904)
- 43128 (MSSQL Server
SELECT CONVERT(INT, CONVERT(DATETIME,GETDATE()))
) - 43129 (Excel)
- 43129 (VBA)
如果您将35天的日期转换为Excel,VBA和MSSQL Server中的日期,结果将如下所示:
If you take the 35. day and convert it to date in Excel, VBA and MSSQL Server, the result will be as follows:
- 1900年2月3日(VBA)
- 1900年2月4日(Excel)
- 1900年2月5日(MSSQL Server-
SELECT CONVERT(DATETIME,35)
) - 1904年2月5日(Excel中带有Date1904)
- 03.February.1900 (VBA)
- 04.February.1900 (Excel)
- 05.February.1900 (MSSQL Server -
SELECT CONVERT(DATETIME,35)
) - 05.February.1904 (Excel with Date1904)
这篇文章(由SO的所有者撰写)还提供了一些其他启示:
我的第一笔BillG评论
This article (written by the owner of SO) gives some addition enlightment: My First BillG Review
这篇关于VBA没有使用Now()返回正确的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!