Solved: Date Format

[phendrena]

Hi there,

For some reason, since the start of October the date format, when it's moved from the user form into the data spreadsheet has imported the date in the format MM/DD/YY instead of DD/MM/YY (UK Localised). For the life of me i can figure out why this happening.

The spreadsheet is setup with the format of DD/MM/YY and the date format in the code is DD/MM/YY.

Any suggestions?

[CreganTur]

Date formatting can be dependant on the local date settings on individual computers. Start there, if that's not the issue then we'll take next steps :thumb

[dcraker]

I am going to point my finger on this one for you.
It could be the issue for you, since the spreadsheet is already going on dd/mm/yy, when there is something entered, it normally follows the format from above.
And when you use the first VBA shown below, it is following the format of the spreadsheet, therefore it is "reverting" again. I couldn't figure out how to set the formatting it, so I could only think of the second entry below. Maybe this helps you or someone to help you.
Since you already have the today's date being entered, my thought would be to change (line 64 on frmCanxData)ws.Cells(iRow, 1).Value = Me.txtDate.Value
to thisws.Cells(iRow, 1).Value = Format(Date, "dd/mm/yy")
FYI: I didn't dig too deep to see if there were other occurences of this. I spotted this and it's what I can see at this time

[MaximS]

Try this:


'Calling out date converting function
Dim Cur_Rng As String
Dim ShtName As String
Dim temp As String
'sample range
Range("C2").Select

Cur_Rng = Range(Selection, Selection.End(xlDown)).Address
ShtName = ActiveSheet.Name
temp = Change_Date(Cur_Rng, ShtName)
If temp <> 0 Then GoTo Er_Fin
Range("C2:C20").NumberFormat = "dd/mm/yyyy;@"
Application.CutCopyMode = False


with function:

Function Change_Date(Adr As String, ShtName As String)

''''''''''''''''''''''''''''
' Date convertion function '
''''''''''''''''''''''''''''
Dim Cur_Sht As String
Dim Cur_Cell As String
On Error GoTo Err_End
Cur_Sht = ActiveSheet.Name
Cur_Cell = ActiveCell.Address
Sheets(ShtName).Select
Range(Adr).Select
Selection.Replace What:=".01.", Replacement:=" Jan ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".02.", Replacement:=" Feb ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".03.", Replacement:=" Mar ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".04.", Replacement:=" Apr ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".05.", Replacement:=" May ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".06.", Replacement:=" Jun ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".07.", Replacement:=" Jul ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".08.", Replacement:=" Aug ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".09.", Replacement:=" Sep ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".10.", Replacement:=" Oct ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".11.", Replacement:=" Nov ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".12.", Replacement:=" Dec ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Change_Date = 0
GoTo Fin
Err_End:
Change_Date = Err.Number
MsgBox "change Date system encountered error: " & Err.Description, _
vbCritical + vbOKOnly, "Fatal Error!"
Fin:
Sheets(Cur_Sht).Select
Range(Cur_Cell).Select
End Function


This should do the job. I am using it to convert the date to format dd/mm/yyyy where data is imported from external database.

Whenever I am trying to format my data as a date (no matter which format) Excel is converting my dates but in US way but only them ones which can be read as both month or day ie. 07/06/2008 - this is converted to 06/07/2008 (dd/mm/yyyy).

[phendrena]

Thank you for the solutions, i;'ve treid them all and i'm happy to report that they work nicely. I do like the complete convert macro - very handy.