When I use the formula datevalue("01/01/1900") I get 1 and formatted as a date it shows as 01/01/1900
当我使用公式datevalue（“01/01/1900”）时，我得到1并格式化为显示为01/01/1900的日期
When I use VBA
当我使用VBA时
.Range("A1").Value = DateValue("01/01/1900")
it shows up as "02/01/1900" in the cell
它在单元格中显示为“02/01/1900”
How is this possible?
这怎么可能？
If i use for example
如果我使用例如
.Range("A1").Value = DateValue("01/01/1901")
it works fine!
它工作正常！
Head Melted!!!
头融化!!!
Microsoft state that - "Using the default date system in Microsoft Excel for Windows, the date_text argument must represent a date between January 1, 1900 and December 31, 9999"
Microsoft声明 - “使用Microsoft Excel for Windows中的默认日期系统，date_text参数必须表示1900年1月1日至9999年12月31日之间的日期”
1 个解决方案
#1
4
In short, Excel's DateTime epoch is not the same as VBA's DateTime epoch. Although, they are the same once you get past February 28th, 1900.
简而言之，Excel的DateTime纪元与VBA的DateTime纪元不同。虽然，一旦你过了1900年2月28日它们就是一样的。
From Joel Spolksy's blog:
来自Joel Spolksy的博客：
In most modern programming environments, dates are stored as real numbers. The integer part of the number is the number of days since some agreed-upon date in the past, called the epoch. In Excel, today's date, June 16, 2006, is stored as 38884, counting days where January 1st, 1900 is 1.
在大多数现代编程环境中，日期存储为实数。该数字的整数部分是自过去某个商定日期以来的天数，称为纪元。在Excel中，今天的日期，即2006年6月16日，存储为38884，计算1900年1月1日为1的日期。
I started working through the various date and time functions in Basic and the date and time functions in Excel, trying things out, when I noticed something strange in the Visual Basic documentation: Basic uses December 31, 1899 as the epoch instead of January 1, 1900, but for some reason, today's date was the same in Excel as it was in Basic.
我开始使用Basic中的各种日期和时间函数以及Excel中的日期和时间函数，尝试解决问题，当我在Visual Basic文档中发现一些奇怪的东西时：Basic使用1899年12月31日作为纪元而不是1月1日， 1900年，但出于某种原因，今天的日期在Excel中与在Basic中相同。
Huh?
咦？
I went to find an Excel developer who was old enough to remember why. Ed Fries seemed to know the answer.
我去找一位年龄足以记住原因的Excel开发人员。艾德弗里斯似乎知道答案。
"Oh," he told me. "Check out February 28th, 1900."
“哦，”他告诉我。 “查看1900年2月28日。”
"It's 59," I said.
“这是59，”我说。
"Now try March 1st."
“现在尝试3月1日。”
"It's 61!"
“现在是61岁！”
"What happened to 60?" Ed asked.
“60岁怎么了？”艾德问道。
"February 29th. 1900 was a leap year! It's divisible by 4!"
“2月29日。1900年是闰年！它可以被4整除！”
"Good guess, but no cigar," Ed said, and left me wondering for a while.
“好猜，但没有雪茄，”艾德说，并让我想了一会儿。
Oops. I did some research. Years that are divisible by 100 are not leap years, unless they're also divisible by 400.
哎呀。我做了一些研究。可以被100整除的年份不是闰年，除非它们也可以被400整除。
1900 wasn't a leap year.
1900年不是闰年。
"It's a bug in Excel!" I exclaimed.
“这是Excel中的一个错误！”我惊呼道。
"Well, not really," said Ed. "We had to do it that way because we need to be able to import Lotus 123 worksheets."
“嗯，不是真的，”埃德说。 “我们必须这样做，因为我们需要能够导入Lotus 123工作表。”
"So, it's a bug in Lotus 123?"
“那么，它是Lotus 123中的一个错误？”
"Yeah, but probably an intentional one. Lotus had to fit in 640K. That's not a lot of memory. If you ignore 1900, you can figure out if a given year is a leap year just by looking to see if the rightmost two bits are zero. That's really fast and easy. The Lotus guys probably figured it didn't matter to be wrong for those two months way in the past. It looks like the Basic guys wanted to be anal about those two months, so they moved the epoch one day back."
“是的，但可能是故意的。莲花必须适合640K。这不是很多记忆。如果你忽略了1900，你可以通过查看最右边的两位来判断一年是否是闰年这真是快而轻松。莲花家伙可能认为过去这两个月的情况并不重要。看起来那些基本的家伙想要在那两个月做肛门，所以他们感动了有一天回来了。“
"Aargh!" I said, and went off to study why there was a checkbox in the options dialog called 1904 Date System.
“AARGH！”我说，然后去研究为什么选项对话框中有一个名为1904 Date System的复选框。
The information below was taken from this Super User answer.
以下信息取自此超级用户答案。
As described in Microsoft KB 214058:
如Microsoft KB 214058中所述：
Days of the week before March 1, 1900 are incorrect in Excel
MORE INFORMATION
When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with date systems used by other spreadsheet programs.
最初创建Microsoft Excel中的日期系统时，它旨在与其他电子表格程序使用的日期系统完全兼容。
However, in this date system, the year 1900 is incorrectly interpreted as a leap year. Because there is no February 29 ("leap day") in the year 1900, the day of the week for any date before March 1, 1900 (the day after the "leap day"), is not computed correctly.
但是，在此日期系统中，1900年被错误地解释为闰年。因为在1900年没有2月29日（“闰日”），所以1900年3月1日（“闰日”之后的那一天）之前的任何日期的星期几都没有正确计算。
The "other spreadsheet programs" refer to Lotus 1-2-3, which was quite popular back then, and incorrectly assumed that year 1900 was a leap year. This is explained in even more detail in KB 214326:
“其他电子表格程序”是指Lotus 1-2-3，当时非常受欢迎，并错误地认为1900年是闰年。这在KB 214326中有更详细的解释：
Excel 2000 incorrectly assumes that the year 1900 is a leap year
MORE INFORMATION
When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.
当Lotus 1-2-3首次发布时，该计划假设1900年是闰年，尽管它实际上不是闰年。这使程序更容易处理闰年，并且对Lotus 1-2-3中的几乎所有日期计算都没有造成任何损害。
When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.
当Microsoft Multiplan和Microsoft Excel发布时，他们还认为1900年是闰年。此假设允许Microsoft Multiplan和Microsoft Excel使用Lotus 1-2-3使用的相同序列日期系统，并提供与Lotus 1-2-3的更高兼容性。将1900年视为闰年也使用户更容易将工作表从一个程序移动到另一个程序。
Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.
虽然技术上可以纠正这种行为，以便当前版本的Microsoft Excel不假设1900是闰年，但这样做的缺点超过了优点。
If this behavior were to be corrected, many problems would arise, including the following:
如果要纠正这种行为，会出现许多问题，包括：
- Almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day. Correcting this shift would take considerable time and effort, especially in formulas that use dates.
- 当前Microsoft Excel工作表和其他文档中的几乎所有日期都将减少一天。纠正这种转变需要花费大量的时间和精力，尤其是在使用日期的公式中。
- Some functions, such as the WEEKDAY function, would return different values; this might cause formulas in worksheets to work incorrectly.
- 某些函数（如WEEKDAY函数）将返回不同的值;这可能会导致工作表中的公式无法正常工作。
- Correcting this behavior would break serial date compatibility between Microsoft Excel and other programs that use dates.
- 更正此行为将破坏Microsoft Excel与使用日期的其他程序之间的序列日期兼容性。
If the behavior remains uncorrected, only one problem occurs:
如果行为仍然未得到纠正，则只会出现一个问题：
- The WEEKDAY function returns incorrect values for dates before March 1, 1900. Because most users do not use dates before March 1, 1900, this problem is rare.
- WEEKDAY函数返回1900年3月1日之前的日期的错误值。由于大多数用户在1900年3月1日之前不使用日期，因此这个问题很少见。
#1
4
In short, Excel's DateTime epoch is not the same as VBA's DateTime epoch. Although, they are the same once you get past February 28th, 1900.
简而言之，Excel的DateTime纪元与VBA的DateTime纪元不同。虽然，一旦你过了1900年2月28日它们就是一样的。
From Joel Spolksy's blog:
来自Joel Spolksy的博客：
In most modern programming environments, dates are stored as real numbers. The integer part of the number is the number of days since some agreed-upon date in the past, called the epoch. In Excel, today's date, June 16, 2006, is stored as 38884, counting days where January 1st, 1900 is 1.
在大多数现代编程环境中，日期存储为实数。该数字的整数部分是自过去某个商定日期以来的天数，称为纪元。在Excel中，今天的日期，即2006年6月16日，存储为38884，计算1900年1月1日为1的日期。
I started working through the various date and time functions in Basic and the date and time functions in Excel, trying things out, when I noticed something strange in the Visual Basic documentation: Basic uses December 31, 1899 as the epoch instead of January 1, 1900, but for some reason, today's date was the same in Excel as it was in Basic.
我开始使用Basic中的各种日期和时间函数以及Excel中的日期和时间函数，尝试解决问题，当我在Visual Basic文档中发现一些奇怪的东西时：Basic使用1899年12月31日作为纪元而不是1月1日， 1900年，但出于某种原因，今天的日期在Excel中与在Basic中相同。
Huh?
咦？
I went to find an Excel developer who was old enough to remember why. Ed Fries seemed to know the answer.
我去找一位年龄足以记住原因的Excel开发人员。艾德弗里斯似乎知道答案。
"Oh," he told me. "Check out February 28th, 1900."
“哦，”他告诉我。 “查看1900年2月28日。”
"It's 59," I said.
“这是59，”我说。
"Now try March 1st."
“现在尝试3月1日。”
"It's 61!"
“现在是61岁！”
"What happened to 60?" Ed asked.
“60岁怎么了？”艾德问道。
"February 29th. 1900 was a leap year! It's divisible by 4!"
“2月29日。1900年是闰年！它可以被4整除！”
"Good guess, but no cigar," Ed said, and left me wondering for a while.
“好猜，但没有雪茄，”艾德说，并让我想了一会儿。
Oops. I did some research. Years that are divisible by 100 are not leap years, unless they're also divisible by 400.
哎呀。我做了一些研究。可以被100整除的年份不是闰年，除非它们也可以被400整除。
1900 wasn't a leap year.
1900年不是闰年。
"It's a bug in Excel!" I exclaimed.
“这是Excel中的一个错误！”我惊呼道。
"Well, not really," said Ed. "We had to do it that way because we need to be able to import Lotus 123 worksheets."
“嗯，不是真的，”埃德说。 “我们必须这样做，因为我们需要能够导入Lotus 123工作表。”
"So, it's a bug in Lotus 123?"
“那么，它是Lotus 123中的一个错误？”
"Yeah, but probably an intentional one. Lotus had to fit in 640K. That's not a lot of memory. If you ignore 1900, you can figure out if a given year is a leap year just by looking to see if the rightmost two bits are zero. That's really fast and easy. The Lotus guys probably figured it didn't matter to be wrong for those two months way in the past. It looks like the Basic guys wanted to be anal about those two months, so they moved the epoch one day back."
“是的，但可能是故意的。莲花必须适合640K。这不是很多记忆。如果你忽略了1900，你可以通过查看最右边的两位来判断一年是否是闰年这真是快而轻松。莲花家伙可能认为过去这两个月的情况并不重要。看起来那些基本的家伙想要在那两个月做肛门，所以他们感动了有一天回来了。“
"Aargh!" I said, and went off to study why there was a checkbox in the options dialog called 1904 Date System.
“AARGH！”我说，然后去研究为什么选项对话框中有一个名为1904 Date System的复选框。
The information below was taken from this Super User answer.
以下信息取自此超级用户答案。
As described in Microsoft KB 214058:
如Microsoft KB 214058中所述：
Days of the week before March 1, 1900 are incorrect in Excel
MORE INFORMATION
When the date system in Microsoft Excel was originally created, it was designed to be fully compatible with date systems used by other spreadsheet programs.
最初创建Microsoft Excel中的日期系统时，它旨在与其他电子表格程序使用的日期系统完全兼容。
However, in this date system, the year 1900 is incorrectly interpreted as a leap year. Because there is no February 29 ("leap day") in the year 1900, the day of the week for any date before March 1, 1900 (the day after the "leap day"), is not computed correctly.
但是，在此日期系统中，1900年被错误地解释为闰年。因为在1900年没有2月29日（“闰日”），所以1900年3月1日（“闰日”之后的那一天）之前的任何日期的星期几都没有正确计算。
The "other spreadsheet programs" refer to Lotus 1-2-3, which was quite popular back then, and incorrectly assumed that year 1900 was a leap year. This is explained in even more detail in KB 214326:
“其他电子表格程序”是指Lotus 1-2-3，当时非常受欢迎，并错误地认为1900年是闰年。这在KB 214326中有更详细的解释：
Excel 2000 incorrectly assumes that the year 1900 is a leap year
MORE INFORMATION
When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.
当Lotus 1-2-3首次发布时，该计划假设1900年是闰年，尽管它实际上不是闰年。这使程序更容易处理闰年，并且对Lotus 1-2-3中的几乎所有日期计算都没有造成任何损害。
When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.
当Microsoft Multiplan和Microsoft Excel发布时，他们还认为1900年是闰年。此假设允许Microsoft Multiplan和Microsoft Excel使用Lotus 1-2-3使用的相同序列日期系统，并提供与Lotus 1-2-3的更高兼容性。将1900年视为闰年也使用户更容易将工作表从一个程序移动到另一个程序。
Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.
虽然技术上可以纠正这种行为，以便当前版本的Microsoft Excel不假设1900是闰年，但这样做的缺点超过了优点。
If this behavior were to be corrected, many problems would arise, including the following:
如果要纠正这种行为，会出现许多问题，包括：
- Almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day. Correcting this shift would take considerable time and effort, especially in formulas that use dates.
- 当前Microsoft Excel工作表和其他文档中的几乎所有日期都将减少一天。纠正这种转变需要花费大量的时间和精力，尤其是在使用日期的公式中。
- Some functions, such as the WEEKDAY function, would return different values; this might cause formulas in worksheets to work incorrectly.
- 某些函数（如WEEKDAY函数）将返回不同的值;这可能会导致工作表中的公式无法正常工作。
- Correcting this behavior would break serial date compatibility between Microsoft Excel and other programs that use dates.
- 更正此行为将破坏Microsoft Excel与使用日期的其他程序之间的序列日期兼容性。
If the behavior remains uncorrected, only one problem occurs:
如果行为仍然未得到纠正，则只会出现一个问题：
- The WEEKDAY function returns incorrect values for dates before March 1, 1900. Because most users do not use dates before March 1, 1900, this problem is rare.
- WEEKDAY函数返回1900年3月1日之前的日期的错误值。由于大多数用户在1900年3月1日之前不使用日期，因此这个问题很少见。