PDA

View Full Version : [SOLVED] Time in VBA



goldie12
08-31-2005, 11:47 AM
Does anyone know how time (HH:MM:SS) is represented in VBA when writing code to search for time? I am trying to count all the instances of an item that's orientation is between 8:00:00 and 4:00:00. Any suggestions?

Cyberdude
08-31-2005, 11:55 AM
Don't forget to use the format "hh:mm:ss AM/PM".
Your search arg should be written something like: 9:15:23 AM
Or perhaps you can use fractions of a 24 hour day. That can work if you can make an easy conversion in your mind. 9:15 AM can in some logic be written as 9.25/24 . It gets harder if you have to include seconds. Just thinking out loud.

mvidas
08-31-2005, 12:05 PM
If the cells you're searching are in normal excel-time mode, you'd look at the numbers to the right of the decimal point.

Dim TheTime As Double, TheCell As Range, TheCount As Long
Set TheCell = ActiveCell
TheTime = TheCell.Value - Int(TheCell.Value)
If TheTime >= 1 / 3 And TheTime <= 2 / 3 Then
TheCount = TheCount + 1
End If
Since excel stores time as the numbers to the right of the decimal point, 8AM = 8/24 = 1/3. 4PM = 16/24 = 2/3
Matt

Bob Phillips
09-01-2005, 02:40 AM
Does anyone know how time (HH:MM:SS) is represented in VBA when writing code to search for time? I am trying to count all the instances of an item that's orientation is between 8:00:00 and 4:00:00. Any suggestions?



MsgBox Application.CountIf(Range("A2:A20"), "<=" & TimeSerial(16, 0, 0)) - _
Application.CountIf(Range("A2:A20"), "<" & TimeSerial(8, 0, 0))

goldie12
09-01-2005, 07:29 AM
Thanks for information. I did not know how time was represented that way. Both solutions work fine.

Zack Barresse
09-01-2005, 09:19 AM
Hi,

When dealing with dates and times, Excel sees a single (24 hour) day as 1. As these are all represented as numerical 'serial numbers', one can use these logically in calculations.

Try entering a date in a cell, then change that format to a number format. The days will be represented by the whole numbers while any time associated with it (none if only a date was entered) will be represented by the fraction (decimals).

So 12 hours equivilates to 0.5 in Excel. To find a time period in Excel, remember that there are 24 hours in a day, 60 minutes in an hour, 1440 minutes in a day (24 x 60), etc. So take hours / 24, or minutes / 1440 etc, to find your 'serial number' equivalent.

Whilst using VBA to determine Dates and Times, there are a couple of constants that you should be aware of. One being Date and the other being Time. These are values in Excel that it will read from the System Date/Time the current OS has in place.

So Date will give you todays date, the same as using the =TODAY() function in the worksheet setting; and Time will give you the current time, the same as the =NOW() function in the worksheet setting.

When dealing with these values in VBA, it's best to use the VBA functions designed for them. The main two being DateSerial and TimeSerial. Use the Intellisense or the Help files to find the correct syntax of each.

Also, one major difference with VBA in dealing with dates, if you declare (dim) a variable as a Date, then it cannot be a number, it must be an Excel recognized date. If you'd like to check if a date is an Excel regocnized date or not, you must use the IsDate function via VBA. We don't (traditionally) have this available via worksheet functions. One workaround for that is here: http://vbaexpress.com/kb/getarticle.php?kb_id=323