Date format from input box - trouble...

[meldrape]

Excel 2000

Hello,

I have a series of input boxes that prompt users to enter dates whose values are put into cells. These dates are used in formulas. I have noticed that if they enter the dates i.e. 3/1/2003 the formulas won't work I guess because it's a static number instead of a date formatted number.
If I enter them as date formats i.e. =DATE(2003,3,1) directly into the cell, the formulas work. Is there a way the user can input the dates into the input boxes in a user friendly manner and programatically can be turned into date formats? This is a major stumbling block. If anybody can help, I'd appreciate it. Thanks a bunch.

 

[tony813]

hello ...

Maybe this will give you an idea:

Sub test()
UserEntry = InputBox("Enter Time&quot

Result = TimeValue(UserEntry)

Worksheets("Sheet1&quot.Range("A1&quot.Value = Result
End Sub Tony813

 

[RobBroekhuis]

You'll need some error handling too - no user likes to be confronted with VBA runtime errors. Better still, use a userform to get the date info, using either specific date controls (Calendar or DTPicker) or event-driven input validation.
Rob

 

[Loomah]

Hi
This line will allow dates in the format dd/mm or dd/mm/yy or dd-mm or dd-mm-yy etc.

But please take heed of Rob's advice. From my experiences dates have always been a pain and an input box is proably nt the best way to get the user to input one!

Worksheets("Sheet1").Range("A1") = CDate(Trim(InputBox("enter date")))

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[meldrape]

Thanks for your suggestions. Do you have any other ideas of getting user input for dates that would be more stable? Thanks.

 

[Loomah]

As I said, see Rob's suggestions. Probably not completely straight forward (I've never used them!) bur I feel sure further explnations will be forthcoming if required!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[blackworx]

If you really want to use inputboxes to get dates, I'd prompt the user with something like "Input the date in the format mm/dd/yyyy" or whatever then validate their input by breaking up the returned string into month, day, year with Left$, Mid$ and Right$.
There's also the IsDate() function which returns TRUE for valid dates.

 

[RobBroekhuis]

Explain more about your application, and we can help find a solution that fits your needs. Do you need to input just one date at a time, a fixed number of dates, or a sequence of dates the number of which cannot be predefined?
Rob

 

[Loomah]

Just been messing around with this and the following test allows every date format I entered and confirmed it as a date, including things like "99 feb" & "99 may 4". I think the input box is better now than it was!! Though still only suitable for single inputs!

Sub a()
Dim resp as String
resp = InputBox("ENTER DATE")
MsgBox IsDate(resp) & " " & CDate(resp)
End Sub

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[RobBroekhuis]

I think that's not the goodness of the input box, but the robustness of the CDate function. Still, you can fool it, and ambiguous entries can mess you up, depending on international settings. Rob

 

[meldrape]

You guys are awesome.

Well, here goes. I have a VLOOKUP: =VLOOKUP(L1,A10:C14,3)

Where L1 is the result of the input box. When I use the date input box, it puts the following date in the cell as 5/1/2003. I get an error in the VLOOKUP formula. Here's the input box code:

Sub NextPayment()
Dim TheString As String
Dim RowNdx As Integer
Dim TheDate As Double
'Next payment
Range("L1&quot.Select
TheString = Application.InputBox("Date Next Payment Due&quot
If IsDate(TheString) Then
TheDate = DateValue(TheString)
ActiveCell = TheDate
Else
MsgBox "Invalid date"
NextPayment
End If
End Sub

If I manually change L1 to =DATE(2003,3,1) the formula works perfectly. That's where I am stuck. I really don't know what to do.

Thanks so much for all of your suggestions.

 

[RobBroekhuis]

I noticed that the date you put in with the inputbox is different from the one you put in manually. Also, that you are using the default (missing) third parameter for the VLOOKUP formula, which requires that your dates are sorted in ascending order. Are your dates sorted? If not, you'll get unexpected results...
Rob

 

[meldrape]

Oops, that was just a typo, it's supposed to be
=DATE(2003,5,1)

The dates in the lookup table are sorted but should I have FALSE in there anyway and see if it makes a difference?

 

[RobBroekhuis]

Depends on whether you need an exact match or an "approximate" match (take a look at the help topic for VLOOKUP to understand the difference). Your problem still puzzles me. Here's a way to troubleshoot: in the VBE immediate window, type

?range("L1&quot*1

the date value should be 37742 regardless of how the date (5/1/03) is entered. Could you check and report back?
Rob

 

[meldrape]

Will do, many thanks, Rob.