Reference Date

[Keith166]

I have StartDate and EndDate fileds where I enter the date and time the employee starts and finishes his shift (in dddd dd mmmm yyyy hh:nn AM/PM format). However there is a penalty issue that starts at 7.00 PM.
How do I set up a non changing reference for 7.00 PM that I can reference to make a calculation.
For example I need to calculate penalties for when the employee works hours past 7.00 cutoff time.

I would appreciate any assistance to solve this problem

Thanks

 

[Guus2005]

After a few tries in the immediate window:

?cdate(39423.79167)
8-12-2007 19:00:00

You can use .79167 to check a user datetime stamp.
The date: 39423 resembles:

?cdate(39423)
8-12-2007

Need more assistance?

 

[Guus2005]

7 PM or 19:00:00 equals 0,791666666666667 because this is 19/24 of an hour.
When you use

if cdbl(enddate) > cdbl(clng(date()) & 19/24) then
   'Exceeds working time, shoot him/her!
else
   'Noneed to kill anyone!
endif

Enjoy!

 

[Guus2005]

7 PM or 19:00:00 equals 0,791666666666667 because this is 19/24 of an hour.
When you use

if cdbl(enddate) > cdbl(clng(date()) & 19/24) then
   'Exceeds working time, shoot him/her!
else
   'Noneed to kill anyone!
endif

Enjoy!

 

[Keith166]

Time reference

Please be patient with me as I'm not a programmer - I don't understand how your solution works. How does cdbl(clng(date()) & 19/24) relate to the time I want to reference. ie I want to enter a Starttime and an Endtime in two fields formatted as I indicated in my previous post. Now, say the normal start time is 05 December 2007 4:00 PM and normal end time is 05 December 2007 8:00 PM and the penalty hours start at 7.00 PM.
How do I subtract 7.00 PM from the end time to calculate how many penalty hours there are regardless of what the end time or date is?

 

[raskew]

Hi -

This MSKB article explains how dates/times are stored in Access.

http://www.access-programmers.co.uk/forums/showthread.php?t=109191

HTH - Bob

 

[Keith166]

Date reference

Bob,
Thanks for your kind reply but it in no way provides a solution to my problem,
Keith

 

[JEA]

Please be patient with me as I'm not a programmer - I don't understand how your solution works. How does cdbl(clng(date()) & 19/24) relate to the time I want to reference. ie I want to enter a Starttime and an Endtime in two fields formatted as I indicated in my previous post. Now, say the normal start time is 05 December 2007 4:00 PM and normal end time is 05 December 2007 8:00 PM and the penalty hours start at 7.00 PM.
How do I subtract 7.00 PM from the end time to calculate how many penalty hours there are regardless of what the end time or date is?

Where are you putting the answer to this calculation?

You've posted in the 'tables' forum. If you want to put the result of this equation in to a table, you shouldn't/can't. Tables shouldn't/can't store calculated data. You need to perform this calculation each time you want to display the result.

So you're going to need to run this calculation in either a form, query or report. By the sounds of it you'll need to do this alot so it may be worth setting up a function in VBA.

I've done something similar:

Public Function MissedHours(sTime As Date, fTime As Date, sType As String)

'Declare variables
Dim Hbefore12 As Date       'Will hold the number of hours WORKED on first day of a night shift.
Dim HAfter12 As Date        'Will hold the number of hours WORKED on second day of a night shift.
Dim HoursWorked As Integer  'Will hold the number of hours a staff member worked
Dim shiftLength As Integer  'Will hold the length that the shift is supposed to be.
Dim sStart As Date          'Will hold the time that the staff member started work.
Dim sFinish As Date         'Will hold the time that the staff member finished work
Dim sHBefore12 As Date      'Will hold the number of hours MENT TO BE WORKED on first day of night shift
Dim sHAfter12 As Date       'Will hold the number of hours MENT TO BE WORKED on second day of night shift

'If any values weren't entered, return 0 to prevent errors
If IsNull(sTime) Or IsNull(fTime) Or IsNull(sType) Then
    MissedHours = 0
Else

'Work out shift length:
sStart = Nz(DLookup("[ShiftStart]", "ShiftType", "[ShiftType]=""" & sType & """"), #12:00:00 AM#)
sFinish = Nz(DLookup("[ShiftFinish]", "ShiftType", "[ShiftType]=""" & sType & """"), #12:00:00 AM#)


If sStart > sFinish Then 'If start time is greater than end time, then shift is split over 2 days.
    sHBefore12 = DateDiff("h", sStart, #11:59:59 PM#) + 1 ' Shift length before 0000 on first day.
    sHAfter12 = DateDiff("h", #12:00:00 AM#, sFinish) 'Shift Length After 0000 on 2nd day.
    shiftLength = sHBefore12 + sHAfter12
Else
    shiftLength = Int(DateDiff("h", sStart, sFinish)) 'Else shift length is the difference between start and finish time.
End If



If sTime > fTime Then           'If StarTime>EndTime it's a night shift over 2 days
    Hbefore12 = DateDiff("h", sTime, #11:59:59 PM#) + 1 'Hours worked before 0000 on 1st day
  
    HAfter12 = DateDiff("h", #12:00:00 AM#, fTime) 'Hours worked After 0000 on 2nd day
    
    HoursWorked = Hbefore12 + HAfter12      'Total hours worked over night shift
    MissedHours = Int(shiftLength - HoursWorked)     'Return missed hours
Else                                   'If StartTime<EndTime the shift was over one day
    HoursWorked = DateDiff("h", sTime, fTime) 'Total Hours Worked
    MissedHours = Int(shiftLength - HoursWorked)     'Return missed hours
End If
End If


End Function

Using this, I enter:

MissedHours(startTimeVariable, finishTimeVariable, shiftTypeVariable)

in a query field or an unbound textbox on a form or report. It will then return or 'equate to' the number of hours that the staff member missed.

In my example I have a seperate table that holds the "ShiftType" with what time it's ment to be started and what time it's ment to be finished. This makes it easier if the shift pattern changes further down the line. You won't have to change your formulae (think that's the plural of formula ) or code, just add a new shift type with new start/finish times to the "Shift Type" table.

How ever you decide to do it, you're going to have to use the DateDiff() function.

Edit:

Looking at your question again, you may want to store the accumulated 'penalty points' for an employee. Use the function that calculates the 'penalty points' in an append query to add a day's penalty points to a table (will prob. be a seperate table just for penalty points, but I don't know your table structure so can't say for certain). You can the run a query on this 'penalty points' table to pull out all the penalty points for a staff member and sum it to get accumulated penalty point.