Forum Home
Press F1
 
Thread ID: 122107 2011-12-01 04:33:00 Excel 2003 - entering/converting date formats Jen (38) Press F1
Post ID Timestamp Content User
1246583 2011-12-04 07:56:00 Thanks guys, I will give those suggestions a whirl tomorrow. :) Jen (38)
1246584 2011-12-04 16:36:00 But wait there's more....

I gave this a shot and it works - the red section actually derives the column you are in so there's no need to specify it

The only thing it does which is funny (but could be a plus at the sane time) - if you enter the date as 032611 it will assume you meany 260311 and it displays 26/03/2011



Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim DateStr As String
UseColumn = Split(ActiveCell(1).Address(1, 0), "$")(0)
On Error GoTo EndMacro '-- CHANGE THE "A" BELOW TO YOUR REQUIRED COLUMN----------
If Application.Intersect(Target, Range(UseColumn & Rows.Count).End(xlUp)) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Formula)
Case 4 ' e.g., 9298 = 2-Sep-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
Case 6 ' e.g., 090298 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
DateStr = Left(.Formula, 1) & "/" & _
Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
Case 8 ' e.g., 09021998 = 2-Sep-1998
DateStr = Left(.Formula, 2) & "/" & _
Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
Case Else
Err.Raise 0
End Select
.Formula = DateValue(DateStr)
End If

End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid date."
Application.EnableEvents = True
End Sub

Trev 		TeejayR (4271)
1246585 2011-12-04 17:14:00 Sorry - forgot to mention that this macro will treat every cell as being a date input.

So unless you want to turn the macro off for standard input (by putting a single quote in front of the Private statement) then Phil B's suggestion would probably be a bit more practical for your use.

Trev 		TeejayR (4271)
1246586 2011-12-05 04:27:00 Hi Jen.. Here's another version. You have to enter the ranges you want manually. You can have up to 30. Hopefully highlighted in red, you can see where to insert/change etc.
If you always use DDMMYY format, it will work reliably.
Have FunWorks super up to the point if you use the proper dd/mm/yyyy format. Then you get, for example, 15/01/12 converting to 04/09/2023. I can't win. :D

Thanks for all your efforts so far - much appreciated. This may just end up in the nice-to-have, but not practical basket. :) 		Jen (38)
1246587 2011-12-05 07:34:00 150112 works ok in mine. 1112 works, it gives 01/01/2012, 11112 gives 1/11/2012. 110112 gives 11/01/2012. My columns are formatted DDMMYYYY, but in enter DDMMYY. As soon as you enter the year in YYYY format, it all goes out the window. Not sure why, as the formulas should be doing the job, but don't. It won't matter unless you want dates prior to 1930.
Have a look
www.4shared.com
You should be having fun by now :badpc: 		Phil B (648)
1 2