Start and Finish time broken up into dynamic time bands calculator 1

[Jade3942]

I'm trying to find a way to perform a series of calculations that would allow me to apportion the number of minutes existing in a time-band (e.g. 1000hrs to 1800hrs) with a given start time and an end time.

Probably best for me to illustrate:

JOB A: Start Time - 0800
JOB A: End Time - 1800

Charge Band one begins 0600 and ends 1200
Charge Band two begins 1200 and ends 1600
Charge Band three begins 1600 and ends 2200

Thus the necessary output would be that

Charge Band One - 4 Hours (240 mins)
Charge Band Two - 4 hours (240 mins)
Charge Band Three - 2 Hours (120 mins)

The example above is a simplified case, there would be a need to accomodate a change over past midnight and the output in minutes between each band would be useful and the number of charge bands is dynamic.

Has anyone seen any example code that accomplishes this above or is their a pre-defined function in MSAccess that allows for it that I have missed?

Any help appreciated...

 

[SkipVought]

Hi,

I'd create a table...
[tt]
Band From Thru
Band1 06:00:00 11:59:59
Band2 12:00:00 15:59:59
Band3 16:00:00 21:59:59
[/tt]
"...accomodate a change over past midnight..."

Date/Time values can be tricky. They must be used together. Date is the integral part; time is the frational part; like right now in North Texas it's 39602.30791. So when you roll past midnight the date part incriments by one.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

You need to provide more information. You only show a time component yet you talk about rolling past midnight.
1) are the times integer values or actual date values?
2) can you go beyond 1 day? Ex:
start 1/1/2008 1000
end 1/3/2008 1200
3) can you enter an interval twice even though you do not go beyond 24 hours? ex
band 0800 - 1200
start 1000
end 0930
thus 2 hours first day, and 1:30 the next day

All of this can be accounted for, but you need to specify buisness rules.

I highly recommend that if you are rolling past midnight that you enter these values as dates with both a date and time component, if not these calculations will be very ambigous.

 

[Jade3942]

Times are entered as hhmm a date component is attached to the same record in a seperate field but if needs be we can combine the fields either in the system OR at the time of calculation. We are using the UK time/date formate in case that has any significance i.e. hhmm and dd/mm/yy

times do not go beyond more than one day, even if they did each day would have it's own set of charge bands so switching from 2300 on monday via midnight would likely cause the minutes worked to be allocated to a band running from 0000-0600 for example on tuesday. A charge band would apply for every minute of every day.

I cannot see an instance where you would need to enter an interval twice as such.

I was hoping this was going to be simpler than it looks but methinks I was being optimistic...doh! any and all help appreciated..

 

[SkipVought]

"Times are entered as hhmm "

That is not a TIME format.

What is an actual time value from your table?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

Like Skip is alluding to the simplest/most flexible way would be to use a variable type of date in your table. The format is actually irrelevant if you are using a "date" field type. Then you can set your format up the way you want it. ex:

start: 03/06/08 13:54

regardless of the format, Access behind the scenes saves 13:54 on the 3 of June 2008 as the number:
39513.5791666667

You can easily work with dates. Multiple fields with integers or strings representing dates and times will be very difficult to work with.

 

[MajP]

I would build a function

Public Function getTimeBand(dtmStart As Date, dtmEnd As Date, dtmBandStart As Date, dtmBandEnd As Date) As Integer
  'You have four possible cases to check
  '1) Time period is completely inside the band
  '2) Time period is bigger than the band
  '3) Time period starts before the band but ends inside the band
  '4) Time peiod starts inside the band but ends after the band
  
  If dtmStart >= dtmBandStart And dtmEnd <= dtmBandEnd Then
     'All inside band
     getTimeBand = DateDiff("n", dtmStart, dtmEnd)
  ElseIf dtmStart <= dtmBandStart And dtmEnd >= dtmBandEnd Then
     'Period bigger than band
    getTimeBand = DateDiff("n", dtmBandStart, dtmBandEnd)
  ElseIf dtmStart <= dtmBandStart And dtmEnd <= dtmBandEnd And dtmEnd >= dtmBandStart Then
     'Period starts before band but ends in band
    getTimeBand = DateDiff("n", dtmBandStart, dtmEnd)
  ElseIf dtmStart >= dtmBandStart And dtmEnd >= dtmBandEnd And dtmStart <= dtmBandEnd Then
     'Period starts after band start and goes beyond band end.
     getTimeBand = DateDiff("n", dtmStart, dtmBandEnd)
  End If
End Function

You can use the function in many ways and you will probably want to wrap it within another function or procedure. For example you could use it in a query. Here is an example of using it in a procedure:

Public Sub TestBand()
  Dim dtmStart As Date
  Dim dtmEnd As Date
  dtmStart = #6/3/2008 3:00:00 AM#
  dtmEnd = #6/4/2008 1:00:00 AM#
  Debug.Print getTimeBand(dtmStart, dtmEnd, #6/3/2008 12:01:00 AM#, #6/3/2008 5:59:00 AM#)
  Debug.Print getTimeBand(dtmStart, dtmEnd, #6/3/2008 6:00:00 AM#, #6/3/2008 11:59:00 AM#)
  Debug.Print getTimeBand(dtmStart, dtmEnd, #6/3/2008 12:00:00 PM#, #6/3/2008 5:59:00 PM#)
  Debug.Print getTimeBand(dtmStart, dtmEnd, #6/3/2008 6:00:00 PM#, #6/3/2008 11:59:00 PM#)
  Debug.Print getTimeBand(dtmStart, dtmEnd, #6/4/2008#, #6/4/2008 5:59:00 PM#)
End Sub

results:
179 (3:00 to 5:59)
359 (6:00 - 11:59)
359 (12:00 - 17:59)
359 (18:00 - 11:59)
60 (12:00 - 01:00 on 4 Jun)

 

[Jade3942]

In connection to this problem above, I've looked at a solution excluding the use of dates.
The pseudocode below would deliver the hours/mins in each band... however not being a tinkerer rather than a coder and being several steps away from testing this I think I might encounter a problem with the stored time value.

The Start-time/End-time field is entered as HH:MM e.g. 22:00 into a field formatted as SHORT-TIME.. you mention that Msaccess would store the value as time-serial, when I call the value in the code I have yet to write I need it to return the value as an integer between 0000-2359 as appropriate.. so 22:00 would be 2200, 14:15 as 1415 etc etc..

I've tested the algorithm below using Excel so I know it outputs the correct values, but of course the values in the Excel test cells were entered as integers automatically.

Would I need to convert the stored value before using it in my calculations? if so how?


PSUEDOCODE BELOW

! Convert StartTime and FinishTime to minutes
! StartMins = (StartTime div 100) * 60
! StartMins = StartMins + (StartTime mod 100)
! FinishMins =(FinishTime div 100) * 60
! FinishMins = FinishMins + (FinishTime mod 100)
! Adjust for midnight
! if StartMins>FinishMins then
FinishMins=FinishMins + 1440
! Calculate Elapsed Time
! Elapsed = FinishMins - StartMins
! Convert Elapsed Time to Hours and Minutes
! ElapsedHours = Elapsed div 60
! ElapsedMins = Elapsed mod 60

Again any insight on this would be appreciated..

 

[MajP]

OK, we are in a little bit of a catch 22.

In connection to this problem above, I've looked at a solution excluding the use of dates.

and

Again any insight on this would be appreciated.

Both Skip and I are MVPs on this site with thousands of responses. We have both recommended that when doing date time calculations the most flexible and easiest method is to use date variable types with a date and time component.

If you want to do it differently then I will bow out. I provided a function that can accept any time band and any time period and report the correct number of minutes. If you want to continue with that approach then, I can show you how you now can wrap my function in another function and use your existing table structure. If interested then you need to answer the following (as both skip and I posed).

Open your table and look at the "Data Type" of fields.
1) What is the data type of your Time fields?
ie. (date / time, number, text)
Do not care about the formatting, it means nothing.
I think you are good because it sounds like the time is saved as a "date / time"
2) What is the data type or your Day / Date field?
3) What does the day field represent (start day only)?
4) Do you have an end day field for a given record?
5) How do you envision passing the information in, and what do you expect to get back for a given record?
6) In your buisness rules you can roll past midnight, put can you go more than 24 hours?
ex Start: 0600 end 0700 (next day)

 

[MajP]

Maybe this will make sense. Lets assume you have three fields in your table
StartDay (date/Time variable)
StartTime (date/Time)
EndTime (date/Time)

(Again formatting is irrelevant)

I could then make a couple of functions for some set bands. Here is one for 0600-1159

Public Function get0600to1159(StartTime As Date, EndTime As Date, StartDate As Date)
  Dim dtmStart As Date
  Dim dtmEnd As Date
  Dim dtmBandStart As Date
  Dim dtmBandEnd As Date
  
  If EndTime < StartTime Then
    EndTime = 2359
  End If
  dtmStart = StartTime + StartDate
  dtmEnd = StartDate + EndTime
  dtmBandStart = StartDate + CDate(#6:00:00 AM#)
  dtmBandEnd = StartDate + CDate(#11:59:00 AM#)

  get0600to1159 = getTimeBand(dtmStart, dtmEnd, dtmBandStart, dtmBandEnd)
End Function

I would use this in a query and pass my fields values in as arguments. The below is a test using some hard coded dates and time.

Public Sub TestBand2()
  Dim StartDate As Date
  Dim EndTime As Date
  Dim StartTime As Date
  StartDate = #6/3/2008#
  StartTime = #2:00:00 AM#
  EndTime = #10:26:00 AM#
  
  Debug.Print get0600to1159(StartTime, EndTime, StartDate)
  
End Sub

and the answer is 266.

Now you can copy and paste that function to make other bands. Just change the interval.

get1200to1759
dtmBandStart = StartDate + CDate(#12:00:00 AM#)
dtmBandEnd = StartDate + CDate(#17:59:00 AM#)

So by wrapping the original function you have the ability to hard code some specific bands, but still have the ability to do a dynamic band.

 

[Jade3942]

The business rules we have in place dictate the following:

Each day of the week is split into timebands for charge and pay purposes, these timebands apply for a given period...e.g Every Monday the hours between 0600-1800 are say paid at £10 per hour, on Tuesdays those same hours could be paid at £5 each etc.. further each timeband applies only to a specific client and a specific skill.
The timebands are user modifiable so they themselves will be stored as records in a table.

Each loaded job has a start/end date(Date/Time field) and stored in another field start/end time (Date/Time field)

I need the system to pull all the timebands that are held for a given client, then select those that apply to a given skill, then select those that apply based on which day of the week it is... returning the number of minutes utilised in each band as it goes.
As we move past midnight it would have to recognise that it must pull the timebands for the next day rather than re-use the early hours bands from the current(now prior) day.

You can never go beyond 24hrs as such any job lasting several days would be reloaded on each day so a 5 day stint one a single task would look like 5 individual jobs to the system.
In terms of hours worked and each record as mentioned above will have an end date in a field seperate from the time element.

Did not mean to upset you by exploring the alternative approach, it seemed simpler as coding is not my strong suit..

 

[MajP]

A start/end date fields and a start/ end Time fields make things easy.

Let me just verify the following.
A five day job would have five entries (records), and these entries could go beyond midnight, but never over 24 hours and never overlapping?

I do not think you can do this in a query, but need to write a procedure to do this. Depending on what you want the output to look like and the user interface. Is this what you are getting after?

1) Prompt the user to provide a reporting period start and end
2) Query the database to return only those job records that fall within that period
3) For each person that has a record in the above query, read their band information from the timebands table.
4)write a record to the output table using DAO/ADO for each persons time band for each day that falls within the reporting period.

Can you provide example input, and what you would like the output to look like? This does not seem too difficult, but there are lots of ways to interpret how the data is returned. If you want help with a procedure to do the above then you will need to provide table and field information.

If you have your tables already, you could hang a small set of data on a file sharing site. There are several free ones out there. It would make things a lot easier.

 

[Craig0201]

You can easily do this sort of calc in queries with zero VBA.

Using a table struct of Jobs(JobID, StartTime, EndTime) and TimeBands(TimeBandID, StartTime, EndTime), you do the following......


qryAppropriateBand (Eliminate those bands which do not apply)

SELECT Jobs.JobID, Jobs.StartTime, Jobs.EndTime, TimeBands.TimeBandID, TimeBands.StartTime, TimeBands.EndTime, [TimeBands]![EndTime]<=[Jobs]![StartTime] AS BandOut1, [TimeBands]![StartTime]>=[Jobs]![EndTime] AS BandOut2
FROM Jobs, TimeBands
WHERE ((([TimeBands]![EndTime]<=[Jobs]![StartTime])=0) AND (([TimeBands]![StartTime]>=[Jobs]![EndTime])=0));

qryCalcRules (Find out whether the time band starts before, is wrapped or finishes after)

SELECT qryAppropriateBands.*, [TimeBands]![StartTime]<[Jobs]![StartTime] AS BandEarly, [TimeBands]![EndTime]>[Jobs]![EndTime] AS BandLate, [Jobs].[StartTime]<=[TimeBands].[StartTime] And [Jobs].[EndTime]>=[TimeBands].[EndTime] AS BandWrapped
FROM qryAppropriateBands;

qryEarlyTime (Calc time for those bands where band starts before job)

SELECT [qryCalcRules].[TimeBands].[EndTime]-[qryCalcRules].[Jobs].[StartTime] AS ChargeTime, qryCalcRules.*
FROM qryCalcRules
WHERE (((qryCalcRules.BandEarly)=-1));

qryWrappedTime (Calc time for those bands where band is wrapped by job)

SELECT ([qryCalcRules].[TimeBands].[EndTime]-[qryCalcRules].[TimeBands].[StartTime]) AS ChargeTime, qryCalcRules.*
FROM qryCalcRules
WHERE (((qryCalcRules.BandWrapped)=-1));

qryLateTime (Calc time where band finishes after job)

SELECT [qryCalcRules].Jobs.EndTime-[qryCalcRules].TimeBands.StartTime AS ChargeTime, qryCalcRules.*
FROM qryCalcRules
WHERE (((qryCalcRules.BandLate)=-1));

Then finally join

qryOutput

SELECT *
FROM [SELECT *
FROM qryEarlyTime
UNION
SELECT *
FROM qryWrappedTime
UNION SELECT *
FROM qryLateTime]. AS a
ORDER BY a.TimeBandID;

This can easily be adapted to jobs over multiple days by splitting the job into 24 hour chunks using a Date Table, a Cartesian job and a couple of comparisons.

But I guess the MVPs knew this.

C

 

[Craig0201]

Oh.....the approach is applicable to variable charging by day too. You just need to add a Day column and match this to the Day function.

Also, note how I used times only. You would need to do a little bit of playing if you are recording the date and time together (as you probably should be) but this is easy enough.

C

 

[MajP]

Craig and Jade, I agree this is the proper approach. I started down the VBA path originally because the initial post had few requirements. I could write a simple function to answer the requirement without knowing anything about the table structure. Also a wrapped function will makes it easy for the user to use, the user just has to pass in the correct parameters. A SQL solution may seem trivial for us contributors, but my guess is that we are going to have to write a lot of it for Jade. This will require some understanding of the table structure. Each subsequent post has revealed more about the table structure and requirements.

Jade, with that said Faq 700-6905 can help you document your tables for us. Then we can write the SQL with the correct names. My guess since you do not profess to be a programmer, it would be very difficult for you to convert Craig's SQL to your table, field names, and relationships. If your are good at SQL then this should make sense.

Craig,
Here is the part that I still can not figure how to handle in SQL, and this is why I was suggesting to code this. Lets say I use my date table to form 24 hour blocks of cartesian jobs. If I have rollover record
Start: 4 June 2200
End: 5 June 0200
How do I handle this? I think even if I had a cartesian job table I need to return basically two records of information. The obvious solution would be to force the user to initally input two records
Start: 4 Jun 2200
end: 4 Jun 2359
Start: 5 Jun 0001
End: 5 Jun 0200
Or write code to do the modify the data and split any rollover records. Your thoughts?

I will be out this weekend so good luck.

 

[MajP]

That is FAQ700-6905

 

[Craig0201]

Quite simple.

You have a Date table which consists of a few thousand rows of dates, say 100 years worth (only 36500 records give or take a few). You then cartesian join your Jobs to the Date table. A compare of the StartDate and EndDate in the Jobs table gives you the appropriate days. You then apply the before, wraps, after logic above to give you the start and finish times.

As an example date table with 5 rows

Jan 1 2000
Jan 2 2000
Jan 3 2000
Jan 4 2000
Jan 5 2000

Job start Jan 2 2000 18:00, end Jan 4 2000 12:00

Queries.....

qryApplicableDateBase

SELECT JobsWithDate.JobID, JobsWithDate.StartTime, JobsWithDate.EndTime, Dates.DateField
FROM JobsWithDate, Dates
WHERE (((Dates.DateField)>=[StartTime]-1 And (Dates.DateField)<=[EndTime]));

qryApplicableDateBandCalcs

SELECT qryApplicableDateBase.*, [StartTime]>[DateField] AS Early, ([EndTime]<[DateField]+1) AS Late, Not (([StartTime]>[DateField]) Or ([EndTime]<[DateField]+1)) AS Wrapped
FROM qryApplicableDateBase;

qryOutputForTimesForNextStep

SELECT IIf([Early]=-1,[StartTime],[DateField]) AS JobDayStartTime, IIf([Late]=-1,[EndTime],[DateField]+1) AS JobDayEndTime, qryApplicableDateBandCalcs.*
FROM qryApplicableDateBandCalcs;

C

 

[MajP]

Thanks. I could not get my head around that concept of using a second query to return the "next step" values.

 

[Jade3942]

I've got to get my head around the above and see how it relates to the implementation side of things... I appreciate all the help thus far, bear with me a couple of days and I'll let you know my progress.