Dates are not being properly formatted

Hello,

I have a date column in an excel spreadsheet that is formatted as mm/dd/yy  Yet when I export it to a csv file, the date is formatted as mm/dd/yyyy .  The system that will be consuming the data, requires that the date be formatted in mm/dd/yy format.  I'm not sure what is causing this.  

Thanks
Juan VelasquezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent DyerIT Security Analyst SeniorCommented:
In Excel, you know you can create your own "custom" format?

Right-Click on the column/Cell..
Format Cells
Select Date
About 4 entries down, locate: 03/14/01

If that does not exist:
Right-Click on the column/Cell..
Format Cells
Select Custom
Type in mm/dd/yy - You may have to use: MM as if I recall correctly, mm is minute minute and not Month Month..

The other thing when you export as CSV, you can define how the columns are exported, and that is probably what you need.

HTH,

Kent
0
Juan VelasquezAuthor Commented:
Yes I know and that's what I did.  However, it looks like excel is storing the date with the year as a four digit year.  I am exporting the file via vba due to coding requirements
0
Kent DyerIT Security Analyst SeniorCommented:
But wait a moment..  What is a CSV file?  It is just a fancy text file..  It does not matter if it comes from Excel or table imported/linked in Access or and ODBC connection..  It is all the same..  Maybe you need to do this from a linked table in Access?

HTH,

Kent
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Juan VelasquezAuthor Commented:
No I can't as the solution has to be an Excel solution , no ands if or buts.  This is what the client requires
0
redmondbCommented:
Hi, chtullu135.

Assuming that the actual date is in A1, put the following in A2...
=TEXT(A1,"mm/dd/yy")

Open in new window

Alternatively, you could use a macro to write the data to your output file.

Regards,
Brian.
0
Juan VelasquezAuthor Commented:
I'm using a macro to write the data to the output file.   I do know when I change the regional settings to MM/dd/yy, I get the properly formatted dates
0
redmondbCommented:
chtullu135,

Assuming that your date is in the variable MyDate, output it as...
Format(MyDate,"MM/DD/YY")

Open in new window

Regards,
Brian.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan VelasquezAuthor Commented:
Hello Edmond,  I've already tried that to no avail
0
redmondbCommented:
chtullu135,

Please post your code here.

A sample file would be good too. (If that's a nuisance, simply copy and paste one of the dates into a new file and post that.)

Thanks,
Brian.
0
Juan VelasquezAuthor Commented:
Hello Brian,

I've attached a sample worksheet with the dates.  However, I think I've come up with a solution.  I'll try it out and let you know if it works
Sample.xlsx
0
redmondbCommented:
Thanks, chtullu135.

FWIW, that's happilly saving to a CSV with YY.

If your plan doesn't work out, please post the code. Also, if you're comfortable with the Immediate pane then just add the following immediately before the line that writes to the output file...
Debug.Print MyDate & " - " & Format(MyDate, "MM/DD/YY") & " - " & MyDate.Formula

Open in new window

... and copy a few of the resulting lines here.

Thanks,
Brian.
0
Juan VelasquezAuthor Commented:
Hello Brian,

I found the solution at
http://en.allexperts.com/q/Excel-1059/2010/9/change-Regional-Setting-short.htm and modified the code slightly so that after the code runs, the regional settings are returned to a four digit year format

Basically I call the procedure chgDate and pass it the format of the regional settings I want, then restore the settings to the MM/dd/yyyy format

Call chgDate("MM/dd/yy")
        Call GenerateOutputFile
        Call chgDate("MM/dd/yyyy"


Option Explicit
Private Const LOCALE_SSHORTDATE = &H1F
Private Const WM_SETTINGCHANGE = &H1A
     
Private Const HWND_BROADCAST = &HFFFF&

Private Declare Function SetLocaleInfo Lib "kernel32" Alias _
         "SetLocaleInfoA" ( _
            ByVal Locale As Long, _
            ByVal LCType As Long, _
            ByVal lpLCData As String) As Boolean
     
Private Declare Function PostMessage Lib "user32" Alias _
         "PostMessageA" ( _
          ByVal hwnd As Long, _
          ByVal wMsg As Long, _
          ByVal wParam As Long, _
          ByVal lParam As Long) As Long
    
Private Declare Function GetSystemDefaultLCID Lib "kernel32" _
          () As Long


Public Sub chgDate(strDateFormat As String)
Dim dwLCID As Long
Dim myDate As String
        
dwLCID = GetSystemDefaultLCID()
        
        
If SetLocaleInfo(dwLCID, LOCALE_SSHORTDATE, strDateFormat) _
     = False Then
     MsgBox "Failed"
     Exit Sub
End If
PostMessage HWND_BROADCAST, WM_SETTINGCHANGE, 0, 0
        
End Sub

Open in new window

0
redmondbCommented:
Thanks, chtullu135.

I'm glad that you're sorted, but that seems a bit excessive just to get a two digit year into a string!

If you have the time, I'd love to see the Debug values.

Thanks,
Brian.
0
Juan VelasquezAuthor Commented:
Hello Brian,

This is the output I've been getting.  The code that I am using is listed below also.  Although the other technique that I implemented working, I'm a little concerned about making changes to the regional settings.  Perhaps some users don't have the privilege level necessary to make changes to their regional settings

09/03/2013 - 09/03/13 - 41520
09/03/2013 - 09/03/13 - 41520
09/04/2013 - 09/04/13 - 41521

 
Public Function RangeToCSV(list As Range) As String
    ' Comments:
    ' Params  : list
    ' Returns : String
    ' Modified:

    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    Dim strTmp As String
    Dim lngCurrentRow As Long
    Dim lngCurrentColumn As Long
    Dim rng As Range
    Dim xlsRange As Range


    If TypeName(list) = "Range" Then
        
            For Each rng In list.Cells
                lngCurrentRow = Range(rng.Address).row
                If Range(rng.Address).Column > 14 Then
                    If rng.row = lngCurrentRow Then
                        If strTmp = vbNullString Then
                            strTmp = rng.value
                            If Range(rng.Address).Column = 23 Then
                                Debug.Print rng.value & " - " & Format(rng.value, "MM/DD/YY") & " - " & rng.Formula
                            End If
                        Else
                            strTmp = strTmp & "," & rng.value
                            If Range(rng.Address).Column = 23 Then
                                Debug.Print rng.value & " - " & Format(rng.value, "MM/DD/YY") & " - " & rng.Formula
                            End If
                        End If
                    Else
                        lngCurrentRow = lngCurrentRow + 1
                        If strTmp = vbNullString Then
                            strTmp = rng.value
                            If Range(rng.Address).Column = 23 Then
                                Debug.Print rng.value & " - " & Format(rng.value, "MM/DD/YY") & " - " & rng.Formula
                            End If
                        Else
                            strTmp = strTmp & Chr(10) & rng.value
                            If Range(rng.Address).Column = 23 Then
                                Debug.Print rng.value & " - " & Format(rng.value, "MM/DD/YY") & " - " & rng.Formula
                            End If
                        End If
                    End If
                End If
             
            Next
    End If

    RangeToCSV = strTmp

    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Application.EnableEvents = True
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.RangeToCSV"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

End Function

Open in new window

0
Juan VelasquezAuthor Commented:
Hello Brian,

I've changed the code so that changing the regional settings is no longer required.  I'm more comfortable with that approach.  Below is the new code
Public Function RangeToCSV(list As Range) As String
    ' Comments:
    ' Params  : list
    ' Returns : String
    ' Modified:

    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    Dim strTmp As String
    Dim lngCurrentRow As Long
    Dim lngCurrentColumn As Long
    Dim rng As Range
    Dim xlsRange As Range


    If TypeName(list) = "Range" Then
        
            For Each rng In list.Cells
                lngCurrentRow = Range(rng.Address).row
                If Range(rng.Address).Column > 14 Then
                    If rng.row = lngCurrentRow Then
                        If strTmp = vbNullString Then
                            If Range(rng.Address).Column = 23 Then
                                strTmp = Format(rng.value, "MM/DD/YY")
                            Else
                                strTmp = rng.value
                            End If
                        Else
                            If Range(rng.Address).Column = 23 Then
                                strTmp = strTmp & "," & Format(rng.value, "MM/DD/YY")
                            Else
                                strTmp = strTmp & "," & rng.value
                            End If
                        End If
                    Else
                        lngCurrentRow = lngCurrentRow + 1
                        If strTmp = vbNullString Then
                            If Range(rng.Address).Column = 23 Then
                                strTmp = Format(rng.value, "MM/DD/YY")
                            Else
                                strTmp = rng.value
                            End If
                        Else
                            If Range(rng.Address).Column = 23 Then
                                strTmp = strTmp & Chr(10) & Format(rng.value, "MM/DD/YY")
                            Else
                                strTmp = strTmp & Chr(10) & rng.value
                            End If
                        End If
                    End If
                End If
             
            Next
    End If

    RangeToCSV = strTmp

    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Application.EnableEvents = True
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.RangeToCSV"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

End Function

Open in new window

0
redmondbCommented:
Thanks, chtullu135.

Yes, that's much cleaner.

Observationally, if the cell is date formatted (either specifically or following the entry of a date) then Value returns a date in the regional settings format. This is what you were seeing originally. However, Format ignores that and simply uses whatever format has been provided.

Regards,
Brian.
0
Juan VelasquezAuthor Commented:
Thanks again Brian.
0
redmondbCommented:
Thanks, chtullu135.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.