How to Program Two Date Ranges?

[Jaynen]

Hello

I have a table with employee names and there start time and stop time for their shift. There is also a start time and stop time which they cant work during their shift. So I have shiftstarttime and shiftstoptime and codestarttime and codestop time. I have to create a table with the employee names and generate a time called outboundtime. This outboundtime starts at a specified time (e.g. 9am) and runs for a specified time (60mins). This outboundtime has to be within the range of the employees shift and cant be within the range of the cant work time. If it was, then it would start after the codestoptime for that employee.

Is anyone able to help me come out with a way to program this. I have been trying "if then statements" but I can't seem to compare a range of time to another range of time. Only a specified time to a range which is making things confusing. I dont think I am taking the right approach to this.

I would greatly appreciate any help! Thanks.

[maximus]

I have some questions:

1) If employees have a variable shift start and end time how is the break period determined? Is it after some particular hours from the start of their shift?
2) What is the outboundtime? Is it the minimum number of hours for which an employee mus work in a shift? If yes what is the minimum number of hours?

Let's take an example here: (Time 24 hours format)

Assumptions:
1) Employees entitled to take a break after 3 hours.
2) Break id for 1 hr
3) OutboundTime is 3 hrs from commencement of shift. (Minimum hours required to work)


Mr. X starts working at 10:00 and assuming. He is entitled to take a break after 3 hrs of continuous work so technically he can take a break at 13:00 so what will be his outbound time here?

I will be glad to help if you will explain you problem in details. Try to say what you want to do I think that will be very helpful. Please provide some examples.

[Jaynen]

Ya its kinda confusing thats why I am having trouble. But I will try and explain this again. Thanks for the questions I do appreciate them.

I have a list of employee names with a shiftstarttime and a shiftstoptime. Between shiftstarttime and shiftstoptime are hours which the employee will be working. Also in that list is a codestarttime and a codestoptime. Code are times when the employee is not working.

Now I need to create whats called outboundtime. Outboundtime is an activity an employee must do DURING their work hours. Outboundtime CAN'T be between the codestarttime and codestoptime. If it is then the outboundtime would start at the codestoptime. Outboundtime length is specified by a textbox so it can be changed, but its usually 60mins. Outboundtime also starts at a time specified by a textbox, usually 9am.


John Doe
outboundtimelength = 60mins
outboundtimestart = 9:00am
shiftstarttime = 8:00am
shiftstoptime = 4:00pm
codestarttime = 9:45am
codestoptime = 10:00am

This would result in...

John Doe take your Outbound Time from 10:00am to 11:00am.

Is there a way I can make a varible equal between times, like...

Codecantwork = between (codestarttime and codestoptime)

Or is that just a wrong approach to this?

[alcapps]

just passing by.. maximus is helping you..

I thought i would offer my observations.. and let maximus finishing helping you..

I assume codestarttime you only have one and they are defined not dynamic. If they are dynamic my suggestion would need to be amended.

you could determine.. the ranges for your outboundstarttime(OBST)
ShiftStartTime(SStartT),
ShiftstopTime(SStopT),
Codestarttime(CStartT),
OutboundTimeLength(OTL)

if (OBST >= SStartT and OBST <= (CStartT-OTL)) or (OBST>=CStopT and OBST <= (SStopT-OTL)) then
its ok to start..
else
outside of the time that it can start
end if

hope this helps

[maximus]

I have created a simple form with unbound text boxes to work out the solution the code attached to a command button prompts the correct Out Bound Start time:

Private Sub Command16_Click()
'This code deals with the basic concept of the time range comparision. This code
'verifies that both tCodeStart and tCodeEnd is not in the range of tOutStart or tOutEnd
'If so the Outbound Time starts at tCodeEnd Time otherwise it will start at tOutStart Time

Dim tCodeStart As Date 'CodeStartTime
Dim tCodeEnd As Date   'CodeEndTime
Dim tOutStart As Date   'OutBoundStart Time Default
Dim tOutEnd As Date     'OutBoundStartTime + OutBoundDuration=OutBoundEndTime

tCodeStart = Format(Me.Text4, "Short Time")
tCodeEnd = Format(Me.Text6, "Short Time")

tOutStart = Format(Me.Text8, "Short Time")
tOutEnd = Format(DateAdd("n", Me.Text10, tOutStart), "Short Time")

MsgBox tOutEnd
Me.Text12 = tOutEnd

If tCodeStart >= tOutStart And tCodeStart <= tOutEnd Or tCodeEnd >= tOutStart And tCodeEnd <= tOutEnd Then

MsgBox "Out Bound Time Start: " & tCodeEnd
Else
MsgBox "Out Bound Time Start: " & tOutStart
End If
End Sub

The assumption here is that the CodeStartTime and CodeEndTime Range if falls within the OutBoundTime Range the OutBoundStartTime will end of CodeEndTime.

Lets see some Test Results:

If CodeStartTime is : 9:45 CodeEndTime is : 10:00 OutBoundStartTime: 9:00 and OutBound Range= 60 min OutBoundEndTime: 10:00
Result: OutBoundStartTime: 10:00

If CodeStartTime is : 9:02 CodeEndTime is : 09:17 OutBoundStartTime: 9:00 and OutBound Range= 60 min OutBoundEndTime: 10:00
Result: OutBoundStartTime: 9:17

If CodeStartTime is : 9:02 CodeEndTime is : 09:17 OutBoundStartTime: 9:00 and OutBound Range= 60 min OutBoundEndTime: 10:00
Result: OutBoundStartTime: 9:17

If CodeStartTime is : 10:15 CodeEndTime is : 10:30 OutBoundStartTime: 9:00 and OutBound Range= 60 min OutBoundEndTime: 10:00
Result: OutBoundStartTime: 9:00

If CodeStartTime is : 9:00 CodeEndTime is : 9:15 OutBoundStartTime: 9:00 and OutBound Range= 60 min OutBoundEndTime: 10:00
Result: OutBoundStartTime: 9:15

You can use this to create a calculative Field to show the OutBoundStartTime

Option Compare Database
Option Explicit
Dim tCodeStart As Date
Dim tCodeEnd As Date
Dim tOutStart As Date
Dim tOutEnd As Date
Dim tOutRange As Integer

Function outBoundTime(tCodeStart, tCodeEnd, tOutStart, tOutRange) As Date

'Syntax: outBountTime([CodeStartTime],[CodeEndTime],[OutBoundStartTime],[OutBoundDuration in Minutes])
'All Time are in 24 Hours Format and OutBoundDuration is integer

tOutEnd = Format(DateAdd("n", tOutRange, tOutStart), "Short Time")
If tCodeStart >= tOutStart And tCodeStart <= tOutEnd Or tCodeEnd >= tOutStart And tCodeEnd <= tOutEnd Then
    outBoundTime = tCodeEnd
Else
    outBoundTime = tOutStart
End If
End Function

[Jaynen]

All the results seem perfect. I am busy today and wont be able to give this a try, but I should be able to tommorow and let you know how it goes. As for now thanks so much for taking the time to reply!