使用替换或替换更改日期格式 [英] Change date format using substitute or replace

问题描述

我正在从一个 excel 文件中读取数据并将数据写入另一个 excel 文件中.

I'm reading data from one excel file and writing data to another excel file.

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

My problem is that the source has dates in format dd.mm.yyyy and the destination must have the format dd-mm-yyyy.

所以我搜索了 SO 并找到了这个答案 用另一个字符替换一个字符在字符串 中，这表明我可以使用 substitute.

So I searched SO and found this answer Replacing one character with another in a string which suggest that I can use substitute.

所以我尝试了这个代码:

So I tried this code:

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'

如何将 . 正确更改为 - ?

How do I correct change the . into - ?

更新

我也试过这个:

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: "-"]'

推荐答案

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

First of all you should know that all dates and times in Excel are stored as numbers and merely shown as dates.

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

So, the number 42,000 could be a date if shown as such. It is the 42,000-th day after December 31, 1899. That would be December 27, 2014.

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

This is where things get complicated: when dates are not stored as numbers but as text in Excel. Then it might seem to an Excel user that there are dates in a cell when in fact there is (for Excel's understanding) only text. While a user cannot possibly tell the difference between the two merely by looking at a cell, the real difference is that you cannot execute and date operations on the text-only dates.

说完这些，让我们看两个小例子:

Having sad all that let's have a look at two small examples:

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

(1) Enter into a cell the following: 11.12.2013. Chances are that Excel will recognize it as a date and will convert it to the number 41,619. Yet, you will see a date. This is because Excel automatically changed the format of that cell from General to Date. But if you change the format of that cell to General then you will get to see the aforementioned number.

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

(2) Now, enter the following into a cell: 2015-14-11. Once again, chances are that Excel will not recognize that as a date but store it as text-only. When you look at the Number format for that cell then you will probably still get to see General and if you are changing it explicitly to Number you still see 2015-14-11.

现在您可能可以更好地理解上面@vacip 的评论.他/她试图找出您的源文件中是否真的有日期(以数字形式存储)，或者您在此文件中是否只有可能暗示它包含日期的文本.

Now you can probably better relate to the comments by @vacip above. He/she was trying to find out if you really actually have dates (stored as numbers) in your source file or if you have merely text in this file which might suggest that it contains dates.

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

If Excel recognized these dates in the source file as such then it is easy to change the format of such simply by changing the .NumberFormat for a cell. In essence, you might want to look into your source file and check if you can show these dates as numbers (the days after December 31, 1899).

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

If that's the case then you should be able to change the number format to dd-mm-yyyy and you're all set. Yet, it is important again if you want to store text in the destination file or if you want to save dates there. If you want to store dates there and you have them as dates in the source file then I'd suggest the following:

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

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

Yet, if you insist on transferring 13.11.2013 into the destination file then you will want to use the following (under the condition that there are actually recognized dates in the source file):

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

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

Where it might get a bit complicated is when you are having text in the source file. If you don't mind that it stays text and you really just want to change the . for a - then you can use the Replace as suggested in a comment above:

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

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

Yet, if you have text in the source file and you want to save dates in the destination file then you can ask Excel to try a conversion with CDate and check upfront if a cell's content could possibly be recognized as a date with 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.有关更多信息，您可能需要阅读以下内容:什么是.text、.value 和 .value2 的区别?

You might have recognized by now that I used sometimes .Value and somethimes .Value2 in the above code snippets. For more on this you might want to read the following: What is the difference between .text, .value, and .value2?

顺便说一句:时间也存储为数字.它们 - 事实上 - 保存为一天的一小部分.因此，数字 0.5 等于半天，即中午 12:00.同时，早上 09:00 为 0.375，而 18:00 或下午 6 点则为 0.75.

BTW: times are also stored as number. They are - in fact - saved as fractions of a day. So, the number 0.5 equates to half a day and that would be 12:00 noon. At the same time, 09:00 in the morning is 0.375 and 18:00 or 6pm equates to 0.75.

这篇关于使用替换或替换更改日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！