excel - 使用替换或替换更改日期格式

Question

我正在从一个 Excel 文件读取数据并将数据写入另一个 Excel 文件。

我的问题是源的日期格式为 dd.mm.yyyy，目标的格式必须为 dd-mm-yyyy。

所以我搜索了所以找到了这个答案 Replacing one character with another in a string这表明我可以使用替代。

所以我尝试了这段代码:

For r = 1 To 10 
    myOutputSheet.Cells(6+r, 1).Value  = myInputSheet.Cells(r, 1).Value 'Date
    myOutputSheet.Cells(6+r, 1).Value  = substitute(myOutputSheet.Cells(6+r, 1), ".", "-")
Next

它给出了错误:

Microsoft VBScript runtime error: Type mismatch: 'substitute'

如何正确地将 . 更改为 - ？

Update

我也尝试过:

For r = 1 To 10 
    myOutputSheet.Cells(6+r, 1).Value  = myInputSheet.Cells(r, 1).Value 'Date
    replace(myOutputSheet.Cells(6+r, 1), 2, 1, "-")
    replace(myOutputSheet.Cells(6+r, 1), 4, 1, "-")
Next

但这给出了错误:

Microsoft VBScript compilation error: Cannot use parentheses when calling a Sub

因此我尝试:

For r = 1 To 10 
    myOutputSheet.Cells(6+r, 1).Value  = myInputSheet.Cells(r, 1).Value 'Date
    replace myOutputSheet.Cells(6+r, 1), 2, 1, "-"
    replace myOutputSheet.Cells(6+r, 1), 4, 1, "-"
Next

但这给出了错误:

Microsoft VBScript runtime error: Type mismatch: '[string: "-"]'

Answer 1

首先，您应该知道 Excel 中的所有日期和时间都存储为数字，并且仅显示为日期。

因此，如果这样显示，数字 42,000 可能是一个日期。这是 1899 年 12 月 31 日之后的第 42,000 天。即 2014 年 12 月 27 日。

这就是事情变得复杂的地方:日期在 Excel 中不是存储为数字而是文本。那么对于 Excel 用户来说，单元格中可能会出现日期，而实际上(为了 Excel 的理解)只有文本。虽然用户不可能仅通过查看单元格来区分两者之间的区别，但真正的区别在于您无法对纯文本日期执行日期操作。

悲伤的一切让我们看两个小例子:

(1) 在单元格中输入以下内容:11.12.2013。 Excel 很可能会将其识别为日期并将其转换为数字 41,619。然而，你会看到一个日期。这是因为 Excel 自动将该单元格的格式从常规更改为日期。但是，如果您将该单元格的格式更改为常规，那么您将看到上述数字。

(2) 现在，在单元格中输入以下内容:2015-14-11。同样，Excel 很可能不会将其识别为日期，而是将其存储为纯文本。当您查看该单元格的数字格式时，您可能仍然会看到 General，如果您将其显式更改为 Number，您仍然会看到 2015- 14-11 .

现在您可能可以更好地理解上面 @vacip 的评论了。他/她试图查明您的源文件中是否确实有日期(存储为数字)，或者该文件中是否只有文本，这可能表明它包含日期。

如果 Excel 能够识别源文件中的这些日期，那么只需更改单元格的 .NumberFormat 即可轻松更改其格式。本质上，您可能需要查看源文件并检查是否可以将这些日期显示为数字(1899 年 12 月 31 日之后的天数)。

如果是这种情况，那么您应该能够将数字格式更改为 dd-mm-yyyy 并且一切都已准备就绪。然而，如果您想在目标文件中存储文本或想在其中保存日期，这一点又很重要。如果您想在那里存储日期并且将它们作为源文件中的日期，那么我建议如下:

myOutputSheet.Cells(6+r, 2).Value2  = myInputSheet.Cells(6+r, 2).Value2
myOutputSheet.Cells(6+r, 2).NumberFormat = "dd-mm-yyyy"

但是，如果您坚持将 13.11.2013 传输到目标文件中，那么您将需要使用以下内容(在源文件中实际识别日期的情况下):

myOutputSheet.Cells(6+r, 2).Value2  = Format(myInputSheet.Cells(6+r, 2).Value2, "dd-mm-yyyy")

当源文件中有文本时，情况可能会变得有点复杂。如果您不介意它保留文本并且您真的只想将 . 更改为 - 那么您可以使用 Replace 作为在上面的评论中建议:

myOutputSheet.Cells(6+r, 2).Value  = Replace(myInputSheet.Cells(6+r, 2).Value, ".", "-")

但是，如果源文件中有文本并且想要在目标文件中保存日期，那么您可以要求 Excel 尝试使用 CDate 进行转换，并预先检查单元格的内容是否可能通过 IsDate 被识别为日期:

myOutputSheet.Cells(6+r, 2).Value2  = Iif(IsDate(myInputSheet.Cells(6+r, 2).Value, CDate(myInputSheet.Cells(6+r, 2).Value), "no recognizable date")

您现在可能已经意识到，我在上面的代码片段中有时使用了 .Value ，有时使用了 .Value2 。有关这方面的更多信息，您可能需要阅读以下内容:What is the difference between .text, .value, and .value2?

顺便说一句:时间也以数字形式存储。事实上，它们被保存为一天的一小部分。因此，数字 0.5 相当于半天，即中午 12:00。同时，早上 09:00 为 0.375，18:00 或下午 6 点相当于 0.75。