Worksheet_Change: Date Time Input [mmddyy hhmm] to [mm/dd/yy hh:mm]

[Brian Handly]

WinXP SP2, Excel XP

I found Chip Pearson's Date Time help:
(http://www.cpearson.com/excel/DateTimeEntry.htm)

However, his Worksheet_Charge example VBA Subroutines are either for
Dates or Times.

I need to handle Date + Time in the same field.

I have tried to combine Chip's two example Subroutines into one, but
have not managed to make a single Subroutine that will handle inputs
like "MMDDYY HHMM" [preferred input]

I have not been able to figure out why Chip's Datevalue Sub uses
".Formula" and the TimeValue Sub uses ".Value" when extracting the user
input.

Suggestions?

Texas Handly

 

[Leith Ross]

Hello Brian,

The easiest method is to create a custom cell format and apply that
format to the cells on the worksheet.

1. Click FORMAT or use ALT + O
2. Click Cells... or press E
3. If the NUMBER tab isn't selected, click it
4. Find CUSTOM in the Category list.
5. Click on the TYPE box or use ALT + T
6. Enter your format as *mm/dd/yyyy hh:mm*
7. Press ENTER or click OK to close the dialog.

Go back to the worksheet and select the cells you want to format and
then repeat steps 1 through 4 to assign your custom format to the
cells.

Sincerely,
Leith Ross

 

[Brian Handly]

Hello Brian,

The easiest method is to create a custom cell format and apply that
format to the cells on the worksheet.

1. Click FORMAT or use ALT + O
2. Click Cells... or press E
3. If the NUMBER tab isn't selected, click it
4. Find CUSTOM in the Category list.
5. Click on the TYPE box or use ALT + T
6. Enter your format as *mm/dd/yyyy hh:mm*
7. Press ENTER or click OK to close the dialog.

Go back to the worksheet and select the cells you want to format and
then repeat steps 1 through 4 to assign your custom format to the
cells.

Sincerely,
Leith Ross

Leith

This would work if EXCEL recognize my input as a date/time.
Unfortunately, EXCEL sees "mmddyy hhmm" as a text field and does not use
your suggested field.

I am looking for a variation of Chip Pearson's subroutines that will
convert my input data ["mmddyy hhmm"] to "mm/dd/yy hh:mm" so that EXCEL
will recognize the data as Date/Time.

Texas Handly

 

[Dave Peterson]

How about this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'MMDDYY HHMM
Dim myRng As Range
Dim myCell As Range
Dim myMonth As Long
Dim myDay As Long
Dim myYear As Long
Dim myHour As Long
Dim myMin As Long

Set myRng = Me.Range("a1:A999")

If Intersect(myRng, Target) Is Nothing Then
Exit Sub
End If

For Each myCell In Intersect(myRng, Target).Cells
With myCell
If .Value Like "###### ####" Then
'keep going
myMonth = Mid(.Value, 1, 2)
myDay = Mid(.Value, 3, 2)
myYear = Mid(.Value, 5, 2)
If myYear < 31 Then
myYear = myYear + 2000
Else
myYear = myYear + 1900
End If
myHour = Mid(.Value, 8, 2)
myMin = Mid(.Value, 10, 2)

If Format(DateSerial(myYear, myMonth, myDay) _
+ TimeSerial(myHour, myMin, 0), "mmddyy hhmm") _
= .Value Then
'it's ok
Application.EnableEvents = False
.Value = DateSerial(myYear, myMonth, myDay) _
+ TimeSerial(myHour, myMin, 0)
.NumberFormat = "mmddyy hhmm"
Application.EnableEvents = True
End If
End If
End With
Next myCell

End Sub

=======
And for the .formula vs .value in Chip's code. I'd just say he wrote each
procedure at different times.

Since he does this:

If .HasFormula = False Then

He knows that the .value and .formula return the exact same thing.

WinXP SP2, Excel XP

I found Chip Pearson's Date Time help:
(http://www.cpearson.com/excel/DateTimeEntry.htm)

However, his Worksheet_Charge example VBA Subroutines are either for
Dates or Times.

I need to handle Date + Time in the same field.

I have tried to combine Chip's two example Subroutines into one, but
have not managed to make a single Subroutine that will handle inputs
like "MMDDYY HHMM" [preferred input]

I have not been able to figure out why Chip's Datevalue Sub uses
".Formula" and the TimeValue Sub uses ".Value" when extracting the user
input.

Suggestions?

Texas Handly

 

[Brian Handly]

How about this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'MMDDYY HHMM
Dim myRng As Range
Dim myCell As Range
Dim myMonth As Long
Dim myDay As Long
Dim myYear As Long
Dim myHour As Long
Dim myMin As Long

Set myRng = Me.Range("a1:A999")

If Intersect(myRng, Target) Is Nothing Then
Exit Sub
End If

For Each myCell In Intersect(myRng, Target).Cells
With myCell
If .Value Like "###### ####" Then
'keep going
myMonth = Mid(.Value, 1, 2)
myDay = Mid(.Value, 3, 2)
myYear = Mid(.Value, 5, 2)
If myYear < 31 Then
myYear = myYear + 2000
Else
myYear = myYear + 1900
End If
myHour = Mid(.Value, 8, 2)
myMin = Mid(.Value, 10, 2)

If Format(DateSerial(myYear, myMonth, myDay) _
+ TimeSerial(myHour, myMin, 0), "mmddyy hhmm") _
= .Value Then
'it's ok
Application.EnableEvents = False
.Value = DateSerial(myYear, myMonth, myDay) _
+ TimeSerial(myHour, myMin, 0)
.NumberFormat = "mmddyy hhmm"
Application.EnableEvents = True
End If
End If
End With
Next myCell

End Sub

=======
And for the .formula vs .value in Chip's code. I'd just say he wrote each
procedure at different times.

Since he does this:

If .HasFormula = False Then

He knows that the .value and .formula return the exact same thing.

WinXP SP2, Excel XP

I found Chip Pearson's Date Time help:
(http://www.cpearson.com/excel/DateTimeEntry.htm)

However, his Worksheet_Charge example VBA Subroutines are either for
Dates or Times.

I need to handle Date + Time in the same field.

I have tried to combine Chip's two example Subroutines into one, but
have not managed to make a single Subroutine that will handle inputs
like "MMDDYY HHMM" [preferred input]

I have not been able to figure out why Chip's Datevalue Sub uses
".Formula" and the TimeValue Sub uses ".Value" when extracting the user
input.

Suggestions?

Texas Handly

Dave

Thanks, Especially for your response on .Formula vs .Value

I will try the Subroutine.

Texas Handly