excel - Change date format using substitute or replace

Question

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

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

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

It gives the error:

Microsoft VBScript runtime error: Type mismatch: 'substitute'

How do I correct change the . into - ?

Update

I tried this as well:

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

but this gives the error:

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

Therefore I tried:

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

but that gives the error:

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

Answer 1

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

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.

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) 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) 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.

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.

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).

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"

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")

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, ".", "-")

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")

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?

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.