title: Change date format using substitute or replace
body: <p>I'm reading data from one excel file and writing data to another excel file.</p>
  
  <p>My problem is that the source has dates in format <code>dd.mm.yyyy</code> and the destination must have the format <code>dd-mm-yyyy</code>.</p>
  
  <p>So I searched SO and found this answer <a href="https://stackoverflow.com/questions/11517628/replacing-one-character-with-another-in-a-string">Replacing one character with another in a string</a> which suggest that I can use <code>substitute</code>.</p>
  
  <p>So I tried this code:</p>
  
  <pre><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
  </code></pre>
  
  <p>It gives the error:</p>
  
  <pre><code>Microsoft VBScript runtime error: Type mismatch: 'substitute'
  </code></pre>
  
  <p>How do I correct change the <code>.</code> into <code>-</code> ?</p>
  
  <blockquote>
    <p>Update</p>
  </blockquote>
  
  <p>I tried this as well:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>but this gives the error:</p>
  
  <pre><code>Microsoft VBScript compilation error: Cannot use parentheses when calling a Sub
  </code></pre>
  
  <p>Therefore I tried:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>but that gives the error:</p>
  
  <pre><code>Microsoft VBScript runtime error: Type mismatch: '[string: "-"]'
  </code></pre>