Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Split Time Period Into Hour Components

Posted on 2004-10-07
7
Medium Priority
?
239 Views
Last Modified: 2012-05-05
I have a series of response times to various jobs. The problem is that these jobs often span across two hours. What I am trying to do is calculate for each hour of the day exactly how many minutes were used for each response time.

I.e:  So if a job started at 6.30 am and finished at 7.10 am i want to assing hour 6 30 minutes of response time and hour 7 10 minutes.

This gets more difficult when the job span three hours, i.e: starts at 6.30 and finishes at 8.10, here I want to assign hour 6 30 mins, hour 7 60 mins and hour 8 10 mins.

Any Ideas????
0
Comment
Question by:GrantLCampbell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 12253858
To get the number of minutes between two dates use DateDiff("m", FirstDateTime, SecondDateTime)

Not sure what you mean by 'assign'.

Hope this helps.
-Jim
0
 
LVL 19

Expert Comment

by:Shauli
ID: 12253971
Private Function HowManyMinutes(ByVal BeginTime As Date, EndTime As Date) As Long
HowManyMinutes = DateDiff("n", BeginTime, EndTime)
End Function

Private Sub Command1_Click()
MsgBox HowManyMinutes("6:30 AM", "8:10 AM")
End Sub

S
0
 

Author Comment

by:GrantLCampbell
ID: 12254023
Sorry guys, not sure I explained m,yself properly, might help I tell you waht I am trying to achevie.

I am trying to work out utilisation for vehicles. At the moment I look at the amount of time a vehcile was working for any given job, if that job starts in one hour and finishes in the next at the moment I attribute all of the job time to the starting hour. The problem is I get a vehicle with a total of 1 hour available working for a total over one hour.

What I am trying to do is split the work time for the vehicle into the hours that it occurs. That way I will not get utilisation over 100%.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 12254040
The logic of this sounds ugly to code, and does not accurately reflect the vehicle's actual usage.  

How exactly would recording the actual usage, in minutes, result in utilization over 100%?
0
 

Author Comment

by:GrantLCampbell
ID: 12254151
The problem occurs because I work out utilisation by hour of day and day of week to implemnt roster design. So when I break utilisation into hourly blocks and a job took longer than the hour available the result is utilisation above 100%.

I am reasonably happy with the accuracy I get from my data now but was wondering if it is possible to apportion the times correctly.
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12254258
It's ugly but - sorry but at  11:30pm I have trouble thinking up variable names:

This function returns a variant array. The first item is the number of minutes in the first hour used, the last is the number of minutes in the final hour used, the ones in the middle (if applicable) are the number of minutes in the intervening hours (always sixty of course)

Public Function GetHourlySomething(ByVal StartTime As Date, ByVal EndTime As Date) As Variant
    Dim nMinutes As Long
    Dim nStartMinute As Long
    Dim nEndMinutes As Long
    Dim vHourlyThings As Variant
    Dim i As Long
   
    ReDim vHourlyThings(0)
   
    nStartMinute = DatePart("n", StartTime)
   
    nMinutes = DateDiff("n", StartTime, EndTime)
   
    If nMinutes + nStartMinute <= 60 Then
        vHourlyThings(0) = nMinutes
        GetHourlySomething = vHourlyThings
        Exit Function
    Else
        vHourlyThings(0) = 60 - nStartMinute
        nMinutes = nMinutes - vHourlyThings(0)
    End If
   
    nEndMinutes = nMinutes Mod 60
   
    ReDim Preserve vHourlyThings(Int(nMinutes / 60) + IIf(nEndMinutes = 0, 0, 1))
   
    For i = 1 To Int(nMinutes / 60)
        vHourlyThings(i) = 60
    Next
   
    If nEndMinutes <> 0 Then
        vHourlyThings(i) = nEndMinutes
    End If
   
    GetHourlySomething = vHourlyThings
End Function
0
 
LVL 10

Accepted Solution

by:
fds_fatboy earned 1000 total points
ID: 12254296
Further: If a job starts at 6.30am and ends at 7.10am the array will be 30,10.
If a job starts at 6.30am and ends at 8.10am the array will be 30,60,10.
If a job starts at 6.30am and ends at 9.10am the array will be 30,60,60,10.
If a job starts at 6.10am and ends at 6.30am the array will be 20.
 
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question