Exclude Certain Times? 1

[sara82]

I have a form which has a Start Time and End time and Log Hours.

Log Hours is calculated as follows:

[LogHours] = Round((DateDiff("n", [Starttime], [Endtime]) / 60), 3)

There are two 15 minute breaks throughout the day. One at 9:45 AM to 10:00 AM and one at 2:45 PM to 3:00 PM. What can I do that if the StartTime and EndTime falls in these times for breaks to exclude them the times of breaks from the StartTime and Endtime of the Employee?

How can I do that in the code?

 

[AlanJordan]

Hi,

I'm not sure if this will work, and I haven't had time to test it, but how about something like this:

Function ExcludeBreakPeriod(datBreakBegin as date, datBreakEnd as Date) as boolean
	
	ExcludeBreakPeriod = True

End Function

Called by something like this:

If LogHours <> ExcludeBreakPeriod("#" & strLoginDate & 9:45:00 AM & "#",  "#" & strLoginDate & 10:00:00 AM & "#")

 

[sara82]

AlanJordan:

Thank you so much for the post. I am unsure however how to get this working.

Is strLoginDate a name that you inserted or is this something that I have to subsitute with my own field name?

What will go after the If statement? If.. Then..?

 

[AlanJordan]

I think that you will need to work on this. Maybe the following will be helpful:

Yes, "strLoginDate" is a variable that you will need to create and properly load with the date. Here's one way to do that.

You might also look at the built in vba functions DatePart, DateSerial and Date Value.

Here's a link to a description of DatePart:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctdatepart.asp

Dim strLoginDate as string
strLoginDate = Format(StartTime, "mm/dd/yyyy")

However, you need to check the logic. Do you really want to have it be the date associated with the StartTime?

My suggestion is to try using the Immediate Window to see the results of various combinations.

To open the Immediate Window, use Ctrl + G.

 

[sara82]

No I wouldn't want the StartTime to be associated with it.

Thank you for the link you provided me. I'm trying to put things together.

I know this is way off but here's something that I need:

Normal Hours
[LogHours] = Round((DateDiff("n", [Starttime], [Endtime]) / 60), 3)

Morning Break
If [StartTime] < 09:45:00 AM And [EndTime] > 10:00:00 AM Then [loghours] = [loghours] - .25

Afternoon Break
If [StartTime] < 02:45:00 PM And [EndTime] > 03:00:00 PM Then [loghours] = [loghours] - .25

 

[PHV]

Are StartTime and EndTime ALWAYS on 15 min boundary ?
I.e. never a time like 09:50:00 AM ?
You may try this:
[LogHours] = Round((DateDiff("n", [Starttime], [Endtime]) / 60), 3) + (([StartTime] <= #09:45:00 AM# And [EndTime] >= #10:00:00 AM#) / 4) + (([StartTime] <= #02:45:00 PM# And [EndTime] >= #03:00:00 PM#) / 4 )

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sara82]

PHV:

Yes it is always on a 15 min boundry as the breaks are always from 9:45 to 10:00 and 2:45 to 3:00.

I implemented your code and it worked wonderfully. Thank you SO very much.

Can you please explain though how it is working just for my knowledge?

 

[PHV]

I've played with the fact that a boolean true value is equal to -1 (false value being equal to 0).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886