Date Format Issue from Input Box

[paddysheeran]

Hi all,
I'm using the following code to get the user to enter the Week commencing date of a report into cell K12 of the Performance_Report sheet. The macro works fine apart from returning the year as 1900. I've searched all over the net and cant find a reason why this is happening - can anyone help?

Sub Date_Entry()
Dim Request As String
Dim ReportTitle As String
Dim DefValue As Date
Dim ReportDate As Date

Request = "Please enter the Week Commencing Date"
ReportTitle = "CEC Weekly Performance Report"
DefValue = Date - 7
ReportDate = Val(InputBox(Request, ReportTitle, DefValue))
Sheets("Performance_Report").Select
Range("K12").Value = ReportDate
Selection.NumberFormat = "dd mmmm yy"
End Sub

[CreganTur]

Welcome to the forum- it's always good to see new members!

When you post code, please wrap it in VBA tags- it will format it according to VBIDE, which makes it easier to read.

The return from an input box is of the string data type. I noticed that you tried to use the Val function to get the date, but the Formt function would be a better fit for you because it actually formats your date. Val is a bad fit for what you're trying to do, as it is designed to only pull the numbers out of a text string. I also set ReportDate to a string format- left as Date format it would overwrite the special formatting that is being applied.
I also removed the selection from your code- selection can cause code slowdowns because it causes changes and events in the UI.

[vba]Sub DateTest()
Dim Request As String
Dim ReportTitle As String
Dim DefValue As Date
Dim ReportDate As String

Request = "Please enter the Week Commencing Date"
ReportTitle = "CEC Weekly Performance Report"
DefValue = Date - 7
ReportDate = Format(CDate(InputBox(Request, ReportTitle, DefValue)), "dd mmmm yy")
ActiveWorkbook.Sheets(1).Range("K12").Value = ReportDate
End Sub
[/vba]

HTH

[Bob Phillips]

[vba]

Sub Date_Entry()
Dim Request As String
Dim ReportTitle As String
Dim DefValue As Date
Dim ReportDate As Date

Request = "Please enter the Week Commencing Date"
ReportTitle = "CEC Weekly Performance Report"
DefValue = Date - 7
ReportDate = CDate(InputBox(Request, ReportTitle, DefValue))
With Sheets("Performance_Report").Range("K12")

.Value = ReportDate
.NumberFormat = "dd mmmm yy"
End With
End Sub
[/vba]

[Kenneth Hobs]

The user can put anything in that so it is no wonder you get odd results.

I would recommend a date control method. You can use this as an example:
http://vbaexpress.com/forum/showthread.php?p=176132

[CreganTur]

The user can put anything in that so it is no wonder you get odd results.

I would recommend a date control method. You can use this as an example:
http://vbaexpress.com/forum/showthread.php?p=176132

That is a much better method, but if there is some reason that you cannot use it, then you should add some validation code to your routine- especially the IsDate function. Take a look at it in Excel Help for all the details.