Trouble formatting a date variable to contain a time value

[Arch Stanton]

I want my user to enter a time into an input box and store the value in
a date variable. Here's my code:

Dim MyTime As Date
MyTime = InputBox("Enter Time", "Time", Format(Time, "hhmm")) / 2400

I have the "/ 2400" on the end because that forces the value entered to
be a time; without that, the value my user enters becomes a date.

The trouble is that the code above returns a value for minutes that's
three fifths of the correct value. It gets the hour correct; for
example, if my user enters "1500", the value is stored correctly. But if
my user enters "1510", the value stored is 1506. In fact, whatever
number of minutes my user enters, the value stored is 3/5 of the correct
value; it sees hours as only 36 minutes long.

Can someone tell me where I've made my mistake? Thanks.

 

[JE McGimpsey]

by dividing by 2400, you're effectively using hours and centihours
(hundredths of hours) rather than hours and minutes.

One alternative:

Dim MyTime As Date
MyTime = TimeValue(Format(Application.InputBox( _
Prompt:="Enter Time", _
Title:="Time", _
Default:=Format(Time, "hhmm"), _
Type:=1), _
"00\:00"))

 

[Arch Stanton]

Your solution works perfectly. Thanks, and thanks also for the hint
about dividing by 2400 (didn't really understand why I was doing that in
the first place).

If I could ask one more thing, what does the slash in the format string
("00\:00") do? I can't find it in VBA Help, but it seems to work.

AS

 

[JE McGimpsey]

In the immediate window, type

?Format(1510,"00\:00")

then

?TimeValue(Format(1510,"00\:00"))