Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force a Date variable to store a time value
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 =---- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force a Date variable to store a time value
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 "Valued Customer" wrote in message ... 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 =---- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force a Date variable to store a time value
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) "NickHK" wrote in message ... 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 "Valued Customer" wrote in message ... 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 =---- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Force a Date variable to store a time value
That worked. Thanks, Bob. Bob Phillips wrote: 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") ----== 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 =---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
STORE THE DATE & TIME THE VALUE HAS CHANGED IN A CELL | Excel Discussion (Misc queries) | |||
worksheet name store in a variable | Excel Programming | |||
store variable | Excel Programming | |||
How to store text to variable | Excel Programming | |||
Store variable value for Chart | Excel Programming |