Excel formatting for date is not working when exporting as XML

Question

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.

Why is the cell alignment for dates different between your two lines of data? Do you have different formats applied? — Tim Williams
– Tim Williams, Commented Jun 14, 2013 at 22:38

Jack · Accepted Answer · 2013-06-19 20:53:10Z

2

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)

edited Jun 19, 2013 at 20:53

answered Jun 14, 2013 at 20:55

Jack

2,8802 gold badges30 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NonProgrammer Over a year ago

but that would still export out as five digit number though right? I want my date to export out as it is (TEXT). I could use another column and do something like: =TEXT(P2,"MM/DD/YYYY"). But then I would be exporting an extra column. I am not familiar with VBA, but is there a way to do this through VBA so the users do not have to do any extra work? Thanks.

Chilion · Accepted Answer · 2015-01-07 15:30:28Z

2

I'm no expert but I spent a lot of time trying to figure this out and eventually this code worked for me

Dim eCell as Range

 For Each eCell In Range("O20:P20")
        If Len(eCell.Value) > 0 Then
          eCell.NumberFormat = "@"
          eCell.Value = CDate(eCell.Value)

        End If
    Next

The CDate function converts the serial date back to normal date

edited Jan 7, 2015 at 15:30

Chilion

4,5304 gold badges36 silver badges48 bronze badges

answered Jan 7, 2015 at 15:02

Caoimhe

211 bronze badge

Comments

NonProgrammer · Accepted Answer · 2013-06-26 18:21:39Z

1

Wow I feel stupid! It was easier than I could have ever thought:

Step1: Drag the date that is in start date to the row you will populate data. (Do the same for Enddate column).

enter image description here

Step2: Notice a small square box at the bottom (it appears after following the step1). Expand the square box.

enter image description here

Step3: Choose the option that says: Fill Formatting Only.

enter image description here

Congrats! Now try to enter/paste a date. It should stay in left (text format).

answered Jun 26, 2013 at 18:21

NonProgrammer

1,3872 gold badges23 silver badges56 bronze badges

Collectives™ on Stack Overflow

Excel formatting for date is not working when exporting as XML

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related