使用替代或替换更改日期格式 [英] 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'

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

更新

我也尝试过这个:

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和somethimes .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或6pm等于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屋！