Typing in dates

[ALLAROUNDOFFICE]

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?

 

[ALLAROUNDOFFICE]

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?

 

[sheetspread]

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?

The code works, but only for values typed in column A (which can easily be expanded). I didn't have a problem after changing between general and date, although 42614 is the date code for 9/01/16 in case you were wondering.

That puts the date in Column C instead of Column A????

Do you mean the formula I posted? That's column B but you can put it in C or anywhere else except where the dates are typed. You can also use the evaluate method in VBA to convert each date with the formula but I'd still go with the code above.

 

[ALLAROUNDOFFICE]

(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>

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.

 

[ALLAROUNDOFFICE]

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)...

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?

 

[Rick Rothstein]

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?

It 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?

 

[ALLAROUNDOFFICE]

It 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

 

[Rick Rothstein]

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

I don't get it... it works for me.

We need help here from the rest of you reading this thread... install the code in Message #20 using the install instruction in Message #4, then type values 040114, 121514 and so on (where the numbers are mmddyy values with date delimiters in them) and tell us if those values are converted to real dates or not.

 

[MARK858]

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.

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

 

[Rick Rothstein]

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

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...

Change this line of code...

Cell.Value = Format(Cell.Value, "0/00/00")

to this...

Cell.Value = Format(Format("042614", "0/00/00"), "d/mm/yy")

 

[MARK858]

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...

It returns 26/04/14 for all cells (btw, I'm fully aware of the VBA setup always being in US format, it's why I use CLng, CDate and DateValue a lot in my coding at work , I was just pointing out my format because of the relevance when you were assessing the results)