2

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: "-"]'
Community
  • 1
  • 1
Support Ukraine
  • 42,271
  • 4
  • 38
  • 63
  • Are the dates actual dates, or strings made to look like dates? (you do understand the difference, don't you?) What does the `.Value2` parameter give you? – vacip May 08 '16 at 14:20
  • @vacip I'm pretty new to VBA but I think they are just strings. – Support Ukraine May 08 '16 at 14:21
  • Don't think. :) Check the value2 property. If it returns a number (42498 for example), then they are actual dates. Or try to manually format them in Excel to a different date format. If nothing happens, they are strings, if they react, they are dates. – vacip May 08 '16 at 14:23
  • Also, `substitute` is an Excel formula. No need for VBA to do that. In VBA, you can use `replace()` for the same effect. – vacip May 08 '16 at 14:24
  • @vacip - When I open the input excel file, right click on the cell and select format, it comes up as `standard` – Support Ukraine May 08 '16 at 14:25
  • Which can mean anything. Try changing the format to a specific date format. – vacip May 08 '16 at 14:26
  • @vacip - I can't change the format (or anything else) in the input excel - it is an excel export from another program. – Support Ukraine May 08 '16 at 14:27
  • ... Sure you can. This is not Excel then, or something is really off. Or you miss some very basic concepts. :( How about the value2? It is hard to help if you miss the fundamental basics... – vacip May 08 '16 at 14:29
  • 1
    Meh, I give up. Use replace instead of substitute, and it should work. Meanwhile, google "date excel" and read and understand. – vacip May 08 '16 at 14:30
  • @vacip - Thanks for trying to help. You are correct I'm missing the basic concepts. As I said I'm new to excel and VBA. I have no idea what you mean by value2. I don't know how to get it. I did google `replace` but it seemed to require that I know the exact location of the characters to change. I would like to say `change every X to Y`. Anyway - thanks for your effort. :-) – Support Ukraine May 08 '16 at 14:33
  • 1
    after you made through the _date-number-string-value-value2_ issue as per @Ralph clever explanation, just remember that 1) if you want to use `Substitute` Excel function then you have to type `WorksheetFunction.Substitute(...)` 2) `Replace` VBA function returns a string so that you want to use it like `myOutputSheet.Cells(6 + r, 1).Value = Replace(myInputSheet.Cells(r, 1), ".", "-")` – user3598756 May 08 '16 at 16:01

1 Answers1

7

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.

Community
  • 1
  • 1
Ralph
  • 9,284
  • 4
  • 32
  • 42