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

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

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 Ross wrote:
> 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

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.

Brian Handly wrote:
>
> 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 Peterson

Dave Peterson wrote:
> 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.
>
> Brian Handly wrote:
>> 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