Excel formatting for date is not working when exporting as XML

excel excel-formula excel-2010 export-to-xml
NonProgrammer picture NonProgrammer · Jun 14, 2013 · Viewed 10k times · Source

I have created an Excel that includes a mapping file. What I am trying to do is have users enter the data in here and press ctrl + E (I created a macro) and it would export it out as XML. Here's the issue: When line 1 gets exported, it keeps its date and time format in XML (Example: In XML, time will show up as 12/01/2013). But anything entered under that line will not show up in the format I want. Instead, it will show up as 1345464 for date.

This thing has been very annoying because I cannot figure out what I can do to format the whole Excel document/column so it will show the date and time as text instead of weird number.

Someone please help me out.

Answer

Jack picture Jack · Jun 14, 2013

In VBA, use the Format function

date = Format(date, "mm/dd/yyyy")

Then change the format of the cell you store it in:

Cells(row, col).NumberFormat = "@"

(substitute your cell's coordinates for row and col)

Related questions

Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Ignore Duplicates and Create New List of Unique Values in Excel

I have a column of values that often appear as duplicates. I need to create a new column, of unique values based on the first column, as follows: Column A Column B a a a b b c c c …

Read More
Excel how to find values in 1 column exist in the range of values in another

I have two columns- column A which extends upto 11027(values) and column I which extends to 42000(values).Both the columns contains some code details. Something like this A B q123 as124 as124 gh456 ff45 q123 dd1 dd2 xx2 xx3 xx4 …

Read More