Date & Time programming

C

cj

Can someone please help? I have some time/date data that
I bring over to Excel from Access that is formatted like
this: 8/30/2004 6:36. What I am trying to accomplish is
to give me the total number of calls say between 6:30 am
and 7:30 am, 7:31 am through 8:30, etc. The problem is
that if I try to extract the time, it gives me weird
numbers (when I format it for numbers, it gives me
38229.27523, which I am assuming is the number value for
Excel for this date). I know how to do VLOOKUPS, but I
can't get it to work because the date/time is not
formatted right. I have tried to format it every way,
but it still doesn't work. Any advice? Thanks!
 
S

sebastienm

Hi cj,
In Excel, one day is 1 unit, so in 38229.27523 the 38229 is trhe date and
0.27523 is the fraction of the day (the time: 1/24 is an hour , 1/(24*60) is
a minute...).

Now, say A1:A35 contains your date/time. Say you want to count hgow many
rows within 2 date/time value entered in A38 and A39:
If A38, A39 are date/time data type, in A40, enter:
=SUMPRODUCT((A1:A35>A38)*(A1:A35<A39)*1)
If A38, A39 are strings representing a date:
=SUMPRODUCT((A1:A35>VALUE(A38))*(A1:A35<VALUE(A39))*1)
(here the string is converted to a date/time using the VALUE() function)

In VBA, you can use the Evaluate function:
Application.Evaluate("=SUMPRODUCT((A1:A35>A38)*(A1:A35<A39)*1)")

In vba, with parameters from vba, something like:
Sub test()
Dim strDate1 As String, strDate2 As String
Dim date1 As Date, date2 As Date
Dim v

strDate1 = "9/9/2004 12:01:50 PM"
strDate2 = "9/9/2004 12:09:02 PM"

date1 = strDate1
date2 = strDate2

v = Application.Evaluate("=SUMPRODUCT((A1:A35>" & CDbl(date1) &
")*(A1:A35<" & CDbl(date2) & ")*1)")
MsgBox v
End Sub

I hope this helps,
Sebastien
 
Top