Excel sheet, using XML as data source and formatting of dates

Question

VBA and date formatting. I've looked everywhere I can think, and haven't found a solution yet. I know it's out there, but am running out of time searching.

I have an Excel sheet that binds to an XML file. The dates in the XML file start out as mm/dd/yyyy. They import into the Excel sheet fine. BUT, if I define the cell as a date format, and then export the data back into my XML file, the file is a serial date, and not the formatted mm/dd/yyyy date I need

Sub FormatDateCells()
      oSh.Cells.Range("I8").NumberFormat = "mm/dd/yyyy"
End Sub

Sub Export_XML()
    Set xmap = ThisWorkbook.XmlMaps("Data_Map")
     If xmap.IsExportable Then
          fname = xmap.DataBinding.SourceUrl
          xmap.Export(fname, True)
     End If
 End Sub

The resulting date in the XML file are SERIAL dates, not in the mm/dd/yyyy format I expect them to be in.

I've already tried formatting the fields using .numberformat = "@", and it gives me the output in the XML file I expect but the user input isn't easy. It's formatted as text.

Bas Verlaat Bas Verlaat · Accepted Answer · 2015-10-07T19:24:04

You could probably add a column with formula

=Text("$A$1","mm/dd/yyyy")

where $A$1 is a reference to your date value. Your date value can be a real date and easy to type, while the new column is a text representation of the date.

Excel sheet, using XML as data source and formatting of dates

1 Answers