Dates are not being properly formatted

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

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

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

No I can't as the solution has to be an Excel solution , no ands if or buts.  This is what the client requires

Hi, chtullu135.

Assuming that the actual date is in A1, put the following in A2...

=TEXT(A1,"mm/dd/yy")

Select allOpen in new window

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

Regards,
Brian.

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

Hello Edmond,  I've already tried that to no avail

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.

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

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

Select allOpen in new window

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

Thanks,
Brian.

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

Select allOpen in new window

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.

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

Select allOpen in new window

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

Select allOpen in new window

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.

Thanks again Brian.

Thanks, chtullu135.