Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date & Time programming
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date & Time programming
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:A35A38)*(A1:A35<A39)*1) If A38, A39 are strings representing a date: =SUMPRODUCT((A1:A35VALUE(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:A35A38)*(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 "cj" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula causing run-time error in Excel Programming | Excel Worksheet Functions | |||
Date programming | Excel Worksheet Functions | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
programming date functions | Excel Worksheet Functions | |||
Date Programming | Excel Programming |