spliting one piece of informaiton into multiple variables (VBA)

[smakatura]

I want to ask a user for a date via an input box.

date = InputBox("enter date of report")

the format of the date will remain consistant mm-dd-yy

and then separate out the parts into 3 variables. so
month = mm
day = dd
year = yy

what vba code would assign the month, day and year from the date input?

 

[AlphaFrog]

Sub Macro1()
    Dim dDate As Date
    Dim y As Long, m As Long, d As Long
    
    dDate = Application.InputBox("Enter date.", Title:="Date Entry", Type:=1)
    If dDate = 0 Then Exit Sub 'User canceled
    
    y = Year(dDate)
    m = Month(dDate)
    d = Day(dDate)
    
    MsgBox "Year=" & y & vbCr & "Month=" & m & vbCr & "Day=" & d

End Sub

 

[tweedle]

something like this

Sub Foo()
'Date is a reserved word [can't be a variable as it's a function]
dtInput = Application.InputBox("enter date of report", Default:=Format(Date, "mm-dd-yy"), Type:=2)
dtMo = Left(dtInput, 2)
dtDa = Mid(dtInput, 4, 2)
dtYr = Right(dtInput, 2)
End Sub

 

[jim may]

This should help...

Sub Foo()
MyDate = InputBox("Enter Date")
res = DateValue(MyDate)
yy = Year(res)
mm = Month(res)
dd = Day(res)
MsgBox mm & dd & yy
End Sub

 

[shg]

You don't want to use date as a variable name.

See the Year, Month, and Day functions.

 

[smakatura]

thanks. I actually had the variable rptdate instead of date. I just up the word date for the forum (now I know not to do that).

next part of the question. on the actual date function where it can be separated out by month ().... is there a particular 'symbol' that has to be between the numbers. ie does it have to be mm/dd/yy or can it also be mm-dd-yy?

It does not matter which one it is but I want to be make sure they write it correctly.

 

[mikerickson]

This code will accept a variety of input formats. "5-23-2011", "5/23/11", "May 23, 2011" "23 May 2011" and some more
If the user types an unacceptable format, it will suggest an acceptable format.

Dim uiDateStr As String
Dim rptDate As Date
Dim strPrompt As String
Dim dd As Long, mm As Long, yy As Long

strPrompt = "Enter a Date"

d0
    uiDateStr = Application.InputBox(strPrompt, Type:=2)
    If uiDateStr = "False" Then Exit Sub: Rem canceled
    strPrompt = "Please enter a date in mm-dd-yyyy format"
Loop Until IsDate(uiDateStr)

rptDate = CDate(uiDateStr)
dd = Day(rptDate): mm = Month(rptDate): yy = Year(rptDate)

 

[smakatura]

I tried all the options

dtInput = Application.InputBox("enter date of report", Default:=Format(Date, "mm-dd-yy"), Type:=2)
dtMo = Left(dtInput, 2)
dtDa = Mid(dtInput, 4, 2)
dtYr = Right(dtInput, 2)

Did not return the values

Sub Macro1()
    Dim dDate As Date
    Dim y As Long, m As Long, d As Long
    
    dDate = Application.InputBox("Enter date.", Title:="Date Entry", Type:=1)
    If dDate = 0 Then Exit Sub 'User canceled
    
    y = Year(dDate)
    m = Month(dDate)
    d = Day(dDate)
    
    MsgBox "Year=" & y & vbCr & "Month=" & m & vbCr & "Day=" & d

End Sub

works but put the month of august (08) as 8. that does not work with opening files that say 08 not 8 in the name

Sub Foo1()
rptdate = InputBox("Enter Date")
res = DateValue(MyDate)
yy = Year(res)
mm = Month(res)
dd = Day(res)
MsgBox mm & dd & yy
End Sub

Got "type mismatch on the res=... line



How would I get the mm, dd, yy variables to say 08 instead of 8?

 

[mikerickson]

In all these codes mm,dd and yy are numbers. Leading zeros are a feature of strings, not numbers.
To display these numbers with leading zeros, one could use the Format function, as in

MsgBox Format(mm,"00")

 

[AlphaFrog]

Sub Open_Dated_Filename()

    Dim dDate As Date
    Dim strFolder As String, strFile As String

    dDate = Application.InputBox("Enter date.", Title:="Date Entry", Type:=1)
    If dDate = 0 Then Exit Sub 'User canceled
    
    strFolder = "C:\Temp\"
    strFile = "MyFile" & Format(dDate, "mmddyyyy") & ".xls"  ' e.g. MyFile09292011.xls
    
    If Len(Dir(strFolder & strFile)) Then
        Workbooks.Open strFolder & strFile
    Else
        MsgBox "Cannot find file: " & strFile, vbExclamation, "File Not Found"
    End If

End Sub