Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have data that has date-time values that I need to convert to a different format. I have created an Excel formula that concatenates the string into exactly the format I need. I currently have the column with the formula in it formatted as TEXT.

However, when I save the data to a CSV file, Excel insists on changing the time portion from 20:22 to 8:22 PM.

How can I get it to save into a CSV file as displayed?

share|improve this question
    
Can you post an example of the data? Values that can be interpreted as Dates by Excel will usually be interpreted that way. FORMATTING the cells does nothing to change this behavior. You need to store the concatenated values in a way that will not be interpreted as Dates. If you can post an example file, or some example data, I can probably help. –  David Zemens Mar 4 '13 at 22:02

2 Answers 2

Sadly, Excel's handling of CSV's has always been rather poor.

The generally accepted solution is to create the CSV file yourself using VBA code. That way you can format things exactly how you want to and you can stop the rather daft thing that Excel does which is to make the quotes around fields optional and sometimes get it wrong. There are a number of reasonable examples of CSV file creation on the Internet and several addins that provide equivalent tools.

Here is some simple code to get you started:

' Output a more consistant CSV file
Public Sub OutputQuotedCSV()
    Const QSTR As String = """"
    Dim myRecord As Range
    Dim myField As Range
    Dim vFilename As Variant
    Dim nFileNum As Long
    Dim sOut As String

    'Get a filename to save as
    vFilename = Application.GetSaveAsFilename(filefilter:="Microsoft CSV files,*.csv", _
    title:="Save as CSV with fields in double quotes")

    If vFilename = False Then Exit Sub 'User chose Cancel

    nFileNum = FreeFile
    Open vFilename For Output As #nFileNum
    For Each myRecord In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        With myRecord
            For Each myField In Range(.Cells(1), Cells(.Row, 256).End(xlToLeft))
                sOut = sOut & "," & QSTR & _
                Replace(myField.Text, QSTR, QSTR & QSTR) & QSTR
            Next myField
            Print #nFileNum, Mid(sOut, 2)
            sOut = Empty
        End With
    Next myRecord
    Close #nFileNum
End Sub

You will need to add a test for dates into that and format accordingly.

share|improve this answer

In my Excel 2007, it does preserve the format required but if necessary 'converting' a time actually stored in A1 as such (ie 0.848611 for 20:22) as below may work [I can't test fully because I am not seeing the symptoms you are!]:

=IF(HOUR(A1)<10,"0"&HOUR(A1)&":"&MINUTE(A1),HOUR(A1)&":"&MINUTE(A1))

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.