Entering Dates MM/YY only

[Guest]

My input box prompts for MM/YY and then I search for that date in column A.
The dates in column A are in MMMM YYYY format and the actual date is always
the first of that month.

When I tried to search using the results of the input box I got nothing -
because when I type in 10/07 Excel thinks I mean 10/07/2007. So I had to
convert the input to a valid date and make sure it was the first of the month.

Here's my code for the whole thing. Is there a better way for me to do this?

**********************
Private Sub Workbook_Open()
Dim EnterDate As String
Dim FindDate As Date
Dim GoHere As Range

' Prompt user for month and year
EnterDate = InputBox( _
"Enter the Month and Year for the data" & _
" being entered (mm/yy)", _
"Enter MM/YY format", Month(Now()) - 1 & "/" & Right(Year(Now()), 2))

' If date entered is not valid display error msg then display input box
again
Do While Not IsDate(EnterDate) And EnterDate <> ""
MsgBox "The value entered is not a date. Please try again."
EnterDate = InputBox("Enter the Month and Year for the data " & _
"being entered (mm/yyyy)", "Enter MM/YYYY format", _
Month(Now()) - 1 & "/" & Right(Year(Now()), 2))
Loop

' Change date to use for searching to be the first of the month entered
If EnterDate = "" Then
MsgBox "User Cancelled"
Else
FindDate = Left(EnterDate, InStr(1, EnterDate, "/") - 1) & "/1/" &
Right(EnterDate, 2)

' Go look in column A for the matching date, move to that cell the
move right two cells
Set rng = Columns(1).Cells
res = Application.Match(CLng(FindDate), rng, 0)
If Not IsError(res) Then
Set GoHere = rng(res)
GoHere.Select
ActiveCell.Offset(0, 2).Select

Else
MsgBox FindDate & " not found"
End If
End If

Exit Sub

addError:

MsgBox "This is not a date."
Resume
End Sub
********************

As always I greatly appreciate the help I get here!!

Lauri S.

 

[smersmensil]

My input box prompts for MM/YY and then I search for that date in column A.
The dates in column A are in MMMM YYYY format and the actual date is always
the first of that month.

When I tried to search using the results of the input box I got nothing -
because when I type in 10/07 Excel thinks I mean 10/07/2007. So I had to
convert the input to a valid date and make sure it was the first of the month.

Here's my code for the whole thing. Is there a better way for me to do this?

**********************
Private Sub Workbook_Open()
Dim EnterDate As String
Dim FindDate As Date
Dim GoHere As Range

' Prompt user for month and year
EnterDate = InputBox( _
"Enter the Month and Year for the data" & _
" being entered (mm/yy)", _
"Enter MM/YY format", Month(Now()) - 1 & "/" & Right(Year(Now()), 2))

' If date entered is not valid display error msg then display input box
again
Do While Not IsDate(EnterDate) And EnterDate <> ""
MsgBox "The value entered is not a date. Please try again."
EnterDate = InputBox("Enter the Month and Year for the data " & _
"being entered (mm/yyyy)", "Enter MM/YYYY format", _
Month(Now()) - 1 & "/" & Right(Year(Now()), 2))
Loop

' Change date to use for searching to be the first of the month entered
If EnterDate = "" Then
MsgBox "User Cancelled"
Else
FindDate = Left(EnterDate, InStr(1, EnterDate, "/") - 1) & "/1/" &
Right(EnterDate, 2)

' Go look in column A for the matching date, move to that cell the
move right two cells
Set rng = Columns(1).Cells
res = Application.Match(CLng(FindDate), rng, 0)
If Not IsError(res) Then
Set GoHere = rng(res)
GoHere.Select
ActiveCell.Offset(0, 2).Select

Else
MsgBox FindDate & " not found"
End If
End If

Exit Sub

addError:

MsgBox "This is not a date."
Resume
End Sub
********************

As always I greatly appreciate the help I get here!!

Lauri S.

Phillip UK London

This works for me

Sub testdate()
Dim EnterDate As Variant
Dim FindDate As Date
Dim OKDate As Boolean
OKDate = False
Do While Not OKDate
EnterDate = _
InputBox("Enter the Month and Year to search in mm/yyyy
format", _
"Enter Data", _
Month(Now()) - 1 & "/" & Right(Year(Now()), 4))
If EnterDate = "" Then
MsgBox "User Cancelled"
Exit Do
ElseIf Not IsDate(EnterDate) Then
MsgBox "The value entered is not a date. Please try
again."
Else
OKDate = True
End If
Loop


' Change user date to the first of the month
If OKDate Then
FindDate = "1/" & EnterDate
' look in column A for the matching date, select two cells to
right from matching date
Set rng = Columns(1).Cells
res = Application.Match(CLng(FindDate), rng, 0)
If Not IsError(res) Then
rng.Cells(1).Offset(res - 1, 2).Select
Else
MsgBox FindDate & " not found"
End If
End If
End Sub

 

[Peter T]

Hi Lauri,

Your approach seems fine though you could have a look at this too, works if
user enters m/yy or d/m/yy (US) with or without leading zeros.

Sub test()
Dim lngDate As Long
Dim sInput As String
Dim va
Dim res As Long
Dim rng As Range

With Range("A7")
..Value = DateSerial(7, 10, 1)
..NumberFormatLocal = "mmmm yyyy"
End With

sInput = "10/07"
'sInput = "10/1/07" ' US m/d/y
'or international
'sInput = "01/10/07" ' d/m/yy


va = Split(sInput, "/")

If UBound(va) >= 2 Then
' user entered */*/*
lngDate = CDate(sInput)
ElseIf UBound(va) = 1 Then

lngDate = DateSerial(va(1), va(0), 1)
Else
' user input error
End If

' handle non valid date 'out of reasonable bounds', eg
If lngDate < DateSerial(1980, 1, 1) Or lngDate > DateSerial(2040, 1, 1) Then
' non valid date
Exit Sub
End If

Set rng = Columns(1).Cells

res = Application.Match(lngDate, rng, 0) ' potential error here if not found

Debug.Print CDate(lngDate)
Debug.Print res

End Sub

Obviously this is trimmed from your routine, and would benefit from adequate
error handling.

I like the Match approach to finding the date in cells. Normally would use
the Find function, which has some advantages but dates can be a bit odd and
require a particular non-intuitive format in the 'what' argument for non US
date users.

Regards,
Peter T

 

[Peter T]

typo -

works if
user enters m/yy or d/m/yy (US) with or without leading zeros.

My date format is International, not US, but I did adjust to cater if US
user enters m/d/yy

Peter T

Your approach seems fine though you could have a look at this too, works if
user enters m/yy or d/m/yy (US) with or without leading zeros.

<snip>

 

[Guest]

Thanks, Peter!! I'm very new to programming in Excel and didn't know about
Split, UBound and DateSerial. That Split will come in very handy!!

I like the Match approach to finding the date in cells.

I learned that from Tom Ogilvy.

Lauri S.

 

[Peter T]

I like the Match approach to finding the date in cells.

I learned that from Tom Ogilvy.

I might have guessed <g>

Regards,
Peter T