Force a Date variable to store a time value

[Valued Customer]

I want my user to enter the time of day into an input box.

My code looks like this:

Dim CollectionTime As Date
CollectionTime = _
InputBox("Enter Collection Time", "Collection Time", Time)

If I run the macro and accept the input box default, the variable
CollectionTime saves a time value in the format "12:00:00 AM". But if I
enter the time in the format "1200" (no quotes), then CollectionTime
stores a seemingly arbitrary date value. It works if I enter a value as
"12:00" (with the colon), but I don't want to waste my users' time by
making them enter the colon.

I've tried the Help files, I've tried the Format function, and I've
Googled it. This is probably the most retarded question ever, but how do
I force a Date variable to store a value like "1500" as a time value?

As always, thanks a bunch.

 

[NickHK]

There is no such thing as a Time data type in Excel/VBA. However, there is a
Date, which is really a double.
The time potion of a Date is stored in the fractional part of the double,
whilst the whole number part of the double stores the days since 30/12/1899
i.e.
?cdate(1)
31/12/1899
?cdate(1.25)
31/12/1899 6:00:00 AM
?now(),cdbl(now()),cdate(cdbl(now()))
13/04/2007 12:27:44 PM 39185.5192592593 13/04/2007 12:27:44
PM

Therefore it up to you how you want to deal with times.
- If there is a day associated with the time, you can simply add them
together, use a date variable.
- Otherwise, ignore the whole number part of the Date and only work with the
fractional part; although you will need to handle what happens when times
span midnight.
- Or use a 2 x Long, for Hours and minutes, and do all the calculations
yourself

Personally, I would arrange for the first option, so you can benefit from
the in built functions; DateDiff, DateAdd, etc.

NickHK

I want my user to enter the time of day into an input box.

My code looks like this:

Dim CollectionTime As Date
CollectionTime = _
InputBox("Enter Collection Time", "Collection Time", Time)

If I run the macro and accept the input box default, the variable
CollectionTime saves a time value in the format "12:00:00 AM". But if I
enter the time in the format "1200" (no quotes), then CollectionTime
stores a seemingly arbitrary date value. It works if I enter a value as
"12:00" (with the colon), but I don't want to waste my users' time by
making them enter the colon.

I've tried the Help files, I've tried the Format function, and I've
Googled it. This is probably the most retarded question ever, but how do
I force a Date variable to store a value like "1500" as a time value?

As always, thanks a bunch.

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption

=----

 

[Bob Phillips]

or get them to put it in 2400 time and convert it

Dim CollectionTime As Date
CollectionTime = _
InputBox("Enter Collection Time", "Collection Time") / 2400
MsgBox Format(CollectionTime, "hh:mm")


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Arch Stanton]

That worked. Thanks, Bob.

or get them to put it in 2400 time and convert it

Dim CollectionTime As Date
CollectionTime = _
InputBox("Enter Collection Time", "Collection Time") / 2400
MsgBox Format(CollectionTime, "hh:mm")