How to format InputBox as Date
Category: [Excel] Demo Available
How to format InputBox as Date
Requirement:
The user has something very strange, the user is trying to format an Input Box to be "mmm-yy"
This the user has done, but when it posts this to the cell, no matter what date the user uses, it defaults the day to the Year, so if the year is 18, and the day is 25th, it will post as 18th
The bit of code the user is using as follows:
Code:
Dim PDate as Variant
PDate = InputBox("What is the Pay Date? - (dd/mm/yy)", "Pay Date") 'What is the current Pay Date
If PDate = "" Then
MsgBox "No Date has been entered" & vbNewLine & _
"The Paydate is still: " & Format([Paydate].Value, "mmm-yy")
Exit Sub
Else
[Paydate].Value = Format(PDate, "mmm-yy")
End If
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1203372-format-inputbox-as-date
Solution:
When prompted, enter the date in yyyy/mm/dd format.
Code:
Sub Test()
Dim PDate As String
PDate = InputBox("What is the Pay Date? - (yyyy/mm/dd)", "Pay Date") 'What is the current Pay Date
If PDate = "" Then
MsgBox "No Date has been entered" & vbNewLine & _
"The Paydate is still: " & Format([Paydate].Value, "mmm-yy")
Exit Sub
Else
[Paydate].Value = CDate(PDate)
End If
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to input a row variable pertaining to all macros |
| How to find and replace based on list entered by user input |
| How to use cell content as input to a structured reference as part of a lookup function |
| How to COUNTIF using input cell as range depth |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.