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
1246573	2011-12-01 04:33:00	Hi all, I've got an Excel 2003 spreadsheet that I want the date if entered as ddmmyy to automatically convert to dd/mm/yyyy. The cells are formatted as *dd/mm/yyyy, but if you enter 010112 it changes to 7/09/1927. How do I do this? I am not looking for a mass convert, but convert on the fly. Thanks :)	Jen (38)
1246574	2011-12-01 06:22:00	Just tried it, and does it here in 2010 as well. Just asked someone who knows more about Excel than I do, and they think its because the cells,are being read by default as a number, even though you enter the date format, so it converts it to the strange date you are getting. If you enter it in 01/01/12 then it puts it in the correct format.:2cents:	wainuitech (129)
1246575	2011-12-01 07:35:00	This spread sheet unfortunately has many different dates to be keyed in - it is faster without the / characters. I need the dates to end up in a standard Excel format because there is a date calculation formula involved. I am just trying to make things easier for staff. I told them it shouldn't be hard to fix. :eek:	Jen (38)
1246576	2011-12-01 09:20:00	It will be something simple to do ( Famous last words) someone who knows will hopefully say -- do "this, that" and its done --- Fingers crossed.	wainuitech (129)
1246577	2011-12-01 09:33:00	Hi Jen.. I can't take full credit for this macro. I found it a while ago & it's one I've used before. I changed it a bit to make it do what I want. I think it may be of use to you www.4shared.com If you go to the above you can download a sample file. It's from Excel 2010 but saved in xlsm. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim DateStr As String On Error GoTo EndMacro '-- CHANGE THE "A" BELOW TO YOUR REQUIRED COLUMN---------- If Application.Intersect(Target, Range("A" & 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 Right click the sheet tab, click View Code & paste it. Change the "A" to another column if you want. You may have to save,close & reopen Excel to make it work. Type in 010112 & you get 01/01/2012 One thing, it does rely on continuous data in each cell going down. You can leave gaps, but if you enter anything in the gaps, it returns gobblydegook. Play with the download file & you'll see what I mean Have Fun	Phil B (648)
1246578	2011-12-02 07:03:00	Thanks for that Phil - it works great.:) However, I need this macro to work in another 20 non-sequential columns. How would I modify the script to add more columns? Cheers.	Jen (38)
1246579	2011-12-03 01:24:00	Tested after posting & it's not working properly	Phil B (648)
1246580	2011-12-03 03:13:00	HI Jen This may be a bit long winded for what you're trying to achieve but it's a way of doing it using an extra column Make sure your data entry column is formatted as text and enter your cells as ddmmyy let's say in this case it is column A with the first date in A1 - tis means you need to enter 03/12/11 as 031211. Column B should have the formula =datevalue(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2)) and be formatted in dd/mm/yyyy format And you simply enter the dates in column A and they get displayed in your desired format in column B Trev	TeejayR (4271)
1246581	2011-12-03 03:45:00	Hi Jen I've had a look at the macro and think I've got it to work for multiple columns I adjusted the code - see the bolded text Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim DateStr As String UseColumn = Range("a1").Value 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 It now works for the column initial that appears in cell a1 - and accepts changes without opening and closing the spreadsheet to re-initialise the macro Trev	TeejayR (4271)
1246582	2011-12-04 07:48: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 Fun Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim DateStr As String On Error GoTo EndMacro '-You can add up to 30 column ranges below---------------------------------- If Application.Intersect(Target, Range("A1:A1000,D1:D1000,F1:F1000,H1:H1000,J1:J1000,K1:K1 000,M1:M1000")) 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) 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 format." Application.EnableEvents = True End Sub	Phil B (648)
1 2