ALLAROUNDOFFICE
Board Regular
- Joined
- Apr 26, 2014
- Messages
- 61
How can I hard code a column of cells to convert the user entered data of "042614" or "42614" into a specific date format such as 04/26/2014?
If install the new event code it puts in 42614 without any dashes or slashes or any date formatting. If I format the column to a date format of 04/26/14 it changes the dates to 09/01/16...??? Still not quite correct. I'd like the dates to display in six digits with slashes. Is that even possible?
That puts the date in Column C instead of Column A????
when I type the above in b1 and b2 it is formatting the information in column c instead of column a. columun a is where I need the dates to appear.(Here's how the formula should have looked anyway)
Excel 2010
A B 1 42814 4/28/2014 2 042814 4/28/2014
<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>Sheet3
Worksheet Formulas
Cell Formula B1 =DATE(RIGHT(A1,2)+2000,LEFT(A1,LEN(A1)-4),MID(A1,LEN(A1)-3,2)) B2 =DATE(RIGHT(A2,2)+2000,LEFT(A2,LEN(A2)-4),MID(A2,LEN(A2)-3,2))
<thead>
</thead><tbody>
</tbody>
<tbody>
</tbody>
After I installed this new event code and enter 42614 in Column A, Excel returns "42614" without any dashes or slashes or any date formatting. If I format the column to a date format of 04/26/14 it changes the dates to 09/01/16...??? Still not quite correct. I'd like the dates to display in six digits with slashes. Is that even possible?I think I see the problem... the first time you put a value in a particular cell, the cell's format changes from General to Date... if you change that cell, the Date format screws things up. Here is my code modified to fix this problem (the line of code I added to fix the problem is shown in red)...
Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range On Error GoTo NothingInColumn If Not Intersect(Target, Columns("A")) Is Nothing Then For Each Cell In Intersect(Target, Intersect(Columns("A"), ActiveSheet.UsedRange)) If Not IsDate(Cell.Value) And Not Cell.Value Like "*[!0-9]*" Then [COLOR=#ff0000][B]Cell.NumberFormat = "General"[/B][/COLOR] Cell.Value = Format(Cell.Value, "0/00/00") End If Next End If NothingInColumn: End Sub
After I installed this new event code and enter 42614 in Column A, Excel returns "42614" without any dashes or slashes or any date formatting. If I format the column to a date format of 04/26/14 it changes the dates to 09/01/16...??? Still not quite correct. I'd like the dates to display in six digits with slashes. Is that even possible?
Tried opening a new worksheet and copied the code. Went back to a1 and entered 040114 and that's all I get. No formatting. Entered 4114 into a2 and I get 4114, again no formatting. Entered 412014 and I get a result of 412014 as a general number as in a1 and a2 - no date formattingIt worked for me when I tested it before posting it. Do me a favor... open a new workbook, put the code into Sheet1's code module and then try entering your values in Column A of that workbook... does the code work correctly for you in it?
Tried opening a new worksheet and copied the code. Went back to a1 and entered 040114 and that's all I get. No formatting. Entered 4114 into a2 and I get 4114, again no formatting. Entered 412014 and I get a result of 412014 as a general number as in a1 and a2 - no date formatting
Excel Workbook | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | 01/04/2014 | 040114 | ||||
2 | 15/12/2014 | 121514 | ||||
3 | 11/06/2014 | 061114 | ||||
4 | 06/04/1911 | 4114 | ||||
5 | 41/20/14 | 412014 | ||||
Sheet5 |
Below are the results in column A, Column D is what I typed in (obviously I have had to enter the ones with leading zero's as text to display correctly).
Please also note that I am in UK date format i.e. dd/mm/yyyy.
Sheet5
A B C D 1 01/04/2014 040114 2 15/12/2014 121514 3 11/06/2014 061114 4 06/04/1911 4114 5 41/20/14 412014
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
Cell.Value = Format(Cell.Value, "0/00/00")
Cell.Value = Format(Format("042614", "0/00/00"), "d/mm/yy")
Thank you... that reminds me of a VBA quirk... I believe it processes all dates in US format. To get UK format (days first, then month, then years), I THINK this will work. Please try it and let me know...