Calculating time difference using working days and working times

[Kevo]

I am fairly new to Access and cannot use VBA.
I normally use Access to work with quantities only, but I am now trying to
calculate time differences and have become stuck.
I am trying to work out the "actual" time it takes to complete an order.
Using the expression

ateDiff("n",[Available date],[Despatch date])/60

But this doesn't take in to consideration our Working days (Monday to
Friday) or working times (06:00 to 20:00).

Say [Available date] = 02/04/2008 07:40:14
and [Despatch date] = 03/04/2008 09:15:21
I get the answer as 25.6 hours. But the "actual" time is only 15.6 hours.
Some orders can take 5 or 6 days to complete.

I need to calculate the total hours actually worked to complete the order

Can anybody please help?
Best Regards,
Kevo

 

[Ken Sheridan]

It would be difficult to do this without using VBA; it would need an
expression of Byzantine complexity. A VBA function is a lot easier. The
following function should do it. All you need to do is paste it into a
standard module in your database:


Public Function TimeWorked(dtmJobStart As Date, _
dtmJobFinish As Date, _
dtmDayStart As Date, _
dtmDayFinish As Date) As Double


Dim dtmDate As Date
Dim lngMinutes As Long
Dim lngMinutesInDay As Long

lngMinutesInDay = DateDiff("n", dtmDayStart, dtmDayFinish)

For dtmDate = DateValue(dtmJobStart) To DateValue(dtmJobFinish)
If Weekday(dtmDate, vbMonday) < 6 Then
If dtmDate = DateValue(dtmJobStart) Then
If TimeValue(dtmJobStart) > dtmDayStart Then
lngMinutes = DateDiff("n", TimeValue(dtmJobStart),
dtmDayFinish)
Else
lngMinutes = lngMinutesInDay
End If
ElseIf dtmDate = DateValue(dtmJobFinish) Then
If TimeValue(dtmJobFinish) < dtmDayFinish Then
lngMinutes = lngMinutes + _
DateDiff("n", dtmDayStart, TimeValue(dtmJobFinish))
Else
lngMinutes = lngMinutes + lngMinutesInDay
End If
Else
lngMinutes = lngMinutes + lngMinutesInDay
End If
End If
Next dtmDate

TimeWorked = lngMinutes / 60

End Function


Make sure you save the module with a different name to the function, e.g.
mdlTimeStuff.

You can then call it, passing the start and end date/time values for the job
and the start and end times for the working day, e.g. in the 'field' row of a
query in design view (all as one line)

HoursWorked:TimeWorked([Available date], [Despatch date],
#06:00:00#,#20:00:00#)

If you try it in the debug window, again as a single line, with your sample
values:

? TimeWorked(#2008-04-02 07:40:14#, #2008-04-03
09:15:21#,#06:00:00#,#20:00:00#)

This returns 15.5833333333333.

Note that date literals must not be in the UK short date format of
dd/mm/yyyy. The US format of mm/dd/yyyy can be used, but I prefer the
unambiguous ISO standard date format of YYYY-MM-DD.

The function does assume continuous time on each day from start to finish,
and does not allow for break time. It also does not allow for overtime if
work starts before 06.00 or finishes after 20.00 on any of the days. It
does, however, exclude weekends from the computation.

Ken Sheridan
Stafford, England

I am fairly new to Access and cannot use VBA.
I normally use Access to work with quantities only, but I am now trying to
calculate time differences and have become stuck.
I am trying to work out the "actual" time it takes to complete an order.
Using the expression

ateDiff("n",[Available date],[Despatch date])/60

But this doesn't take in to consideration our Working days (Monday to
Friday) or working times (06:00 to 20:00).

Say [Available date] = 02/04/2008 07:40:14
and [Despatch date] = 03/04/2008 09:15:21
I get the answer as 25.6 hours. But the "actual" time is only 15.6 hours.
Some orders can take 5 or 6 days to complete.

I need to calculate the total hours actually worked to complete the order

Can anybody please help?
Best Regards,
Kevo

 

[James A. Fortune]

I am fairly new to Access and cannot use VBA.
I normally use Access to work with quantities only, but I am now trying to
calculate time differences and have become stuck.
I am trying to work out the "actual" time it takes to complete an order.
Using the expression

ateDiff("n",[Available date],[Despatch date])/60

But this doesn't take in to consideration our Working days (Monday to
Friday) or working times (06:00 to 20:00).

Say [Available date] = 02/04/2008 07:40:14
and [Despatch date] = 03/04/2008 09:15:21
I get the answer as 25.6 hours. But the "actual" time is only 15.6 hours.
Some orders can take 5 or 6 days to complete.

I need to calculate the total hours actually worked to complete the order

Can anybody please help?
Best Regards,
Kevo

You can filter out nonscheduled work hours by having a table of 15
minute "breaks" that include actual 15 minute breaks, lunch breaks and
hours before or after the "shift."

tblI
ID AutoNumber
I Long
ID I
1 1
2 2
3 3
4 4
5 5
6 6
7 7

tblTimeTickets
TTID AutoNumber
PunchIn Date/Time
PunchOut Date/Time
TTID PunchIn PunchOut
1 4/20/2009 11:15:00 AM 4/21/2009 11:30:00 AM
2 4/22/2009 9:30:00 AM 4/22/2009 5:30:00 PM

tblBreaks
BID AutoNumber
BreakStart Date/Time Format h:nn ampm
BreakEnd Date/Time Format h:nn ampm
BID BreakStart BreakEnd
1 12:00 AM 12:15 AM
2 12:15 AM 12:30 AM
....
34 7:30 AM 7:45 AM
35 7:45 AM 8:00 AM
36 10:00 AM 10:15 AM
37 12:00 PM 12:15 PM
38 12:15 PM 12:30 PM
39 12:30 PM 12:45 PM
40 12:45 PM 1:00 PM
41 3:00 PM 3:15 PM
42 5:00 PM 5:15 PM
43 5:15 PM 5:30 PM
....
68 11:30 PM 11:45 PM
69 11:45 PM 12:00 AM

qryBreaks:
SELECT TTID, BreakEnd, PunchIn, PunchOut
FROM tblTimeTickets, tblBreaks, tblI
GROUP BY TTID, BreakEnd, PunchIn, PunchOut,
DateAdd("d",-1,DateValue(PunchIn))+[BreakEnd]
HAVING (((DateAdd("d",-1,DateValue(PunchIn))+[BreakEnd]) Between
[PunchIn] And [PunchOut]));

qryCountBreaks:
SELECT TTID, Count(*) AS CountBreaks
FROM qryBreaks
GROUP BY TTID;

!qryCountBreaks:
TTID CountBreaks
1 66
2 8

qryHoursWorked:
SELECT PunchIn, PunchOut, (DateDiff("n", PunchIn, PunchOut) - 15 *
(SELECT Sum(A.CountBreaks) AS SumOfCountBreaks
FROM qryCountBreaks AS A WHERE A.TTID = B.TTID)) / 60 AS HoursWorked
FROM tblTimeTickets AS B;

!qryHoursWorked
PunchIn PunchOut HoursWorked
4/20/2009 11:15:00 AM 4/21/2009 11:30:00 AM 7.75
4/22/2009 9:30:00 AM 4/22/2009 5:30:00 PM 6

The first time ticket goes from 11:15 am on Monday until 11:30 am on
Tuesday. The time worked is 11:15 am to 12 pm, 1 pm to 3 pm, 3:15 pm to
5 pm, 8 am to 10 am, 10:15 am to 11:30 am for a total of 7.75 hours.

The second time ticket goes from 9:30 am on Wednesday until 5:30 pm on
the same day. The time worked is 9:30 am to 10 am, 10:15 am to 12 pm, 1
pm to 3 pm, 3:15 pm to 5 pm for a total of 6 hours.

A purer way, in my view at least, to write qryBreaks is:

qryBreaks:

SELECT TTID, BreakEnd, PunchIn, PunchOut
FROM tblTimeTickets, tblBreaks, tblI
GROUP BY TTID, BreakEnd, PunchIn, PunchOut, DateAdd("n", DateDiff("n",
TimeValue(CDate(0)), [BreakEnd]), DateAdd("d",-1,DateValue(PunchIn)))
HAVING DateAdd("n", DateDiff("n", TimeValue(CDate(0)), [BreakEnd]),
DateAdd("d",-1,DateValue(PunchIn))) Between [PunchIn] And [PunchOut];

I'm not sure about how to exclude weekend hours using just SQL in a
simple way. Perhaps the following VBA will give you some ideas:

http://groups.google.com/group/comp.databases.ms-access/msg/548d33651b087c05

James A. Fortune
(e-mail address removed)

SWEEPER - A train that stops at all stations. -- Dictionary of
Australian Slang, Second Edition, Sidney J. Baker, 1943 (Price: Three
shillings & sixpence)

 

[James A. Fortune]

I am fairly new to Access and cannot use VBA.
I normally use Access to work with quantities only, but I am now
trying to calculate time differences and have become stuck.
I am trying to work out the "actual" time it takes to complete an order.
Using the expression

ateDiff("n",[Available date],[Despatch date])/60

But this doesn't take in to consideration our Working days (Monday to
Friday) or working times (06:00 to 20:00).

Say [Available date] = 02/04/2008 07:40:14
and [Despatch date] = 03/04/2008 09:15:21
I get the answer as 25.6 hours. But the "actual" time is only 15.6
hours.
Some orders can take 5 or 6 days to complete.

I need to calculate the total hours actually worked to complete the order

Can anybody please help? Best Regards,
Kevo

You can filter out nonscheduled work hours by having a table of 15
minute "breaks" that include actual 15 minute breaks, lunch breaks and
hours before or after the "shift."

tblI
ID AutoNumber
I Long
ID I
1 1
2 2
3 3
4 4
5 5
6 6
7 7

tblTimeTickets
TTID AutoNumber
PunchIn Date/Time
PunchOut Date/Time
TTID PunchIn PunchOut
1 4/20/2009 11:15:00 AM 4/21/2009 11:30:00 AM
2 4/22/2009 9:30:00 AM 4/22/2009 5:30:00 PM

tblBreaks
BID AutoNumber
BreakStart Date/Time Format h:nn ampm
BreakEnd Date/Time Format h:nn ampm
BID BreakStart BreakEnd
1 12:00 AM 12:15 AM
2 12:15 AM 12:30 AM
...
34 7:30 AM 7:45 AM
35 7:45 AM 8:00 AM
36 10:00 AM 10:15 AM
37 12:00 PM 12:15 PM
38 12:15 PM 12:30 PM
39 12:30 PM 12:45 PM
40 12:45 PM 1:00 PM
41 3:00 PM 3:15 PM
42 5:00 PM 5:15 PM
43 5:15 PM 5:30 PM
...
68 11:30 PM 11:45 PM
69 11:45 PM 12:00 AM

qryBreaks:
SELECT TTID, BreakEnd, PunchIn, PunchOut
FROM tblTimeTickets, tblBreaks, tblI
GROUP BY TTID, BreakEnd, PunchIn, PunchOut,
DateAdd("d",-1,DateValue(PunchIn))+[BreakEnd]
HAVING (((DateAdd("d",-1,DateValue(PunchIn))+[BreakEnd]) Between
[PunchIn] And [PunchOut]));

qryCountBreaks:
SELECT TTID, Count(*) AS CountBreaks
FROM qryBreaks
GROUP BY TTID;

!qryCountBreaks:
TTID CountBreaks
1 66
2 8

qryHoursWorked:
SELECT PunchIn, PunchOut, (DateDiff("n", PunchIn, PunchOut) - 15 *
(SELECT Sum(A.CountBreaks) AS SumOfCountBreaks
FROM qryCountBreaks AS A WHERE A.TTID = B.TTID)) / 60 AS HoursWorked
FROM tblTimeTickets AS B;

!qryHoursWorked
PunchIn PunchOut HoursWorked
4/20/2009 11:15:00 AM 4/21/2009 11:30:00 AM 7.75
4/22/2009 9:30:00 AM 4/22/2009 5:30:00 PM 6

The first time ticket goes from 11:15 am on Monday until 11:30 am on
Tuesday. The time worked is 11:15 am to 12 pm, 1 pm to 3 pm, 3:15 pm to
5 pm, 8 am to 10 am, 10:15 am to 11:30 am for a total of 7.75 hours.

The second time ticket goes from 9:30 am on Wednesday until 5:30 pm on
the same day. The time worked is 9:30 am to 10 am, 10:15 am to 12 pm, 1
pm to 3 pm, 3:15 pm to 5 pm for a total of 6 hours.

A purer way, in my view at least, to write qryBreaks is:

qryBreaks:

SELECT TTID, BreakEnd, PunchIn, PunchOut
FROM tblTimeTickets, tblBreaks, tblI
GROUP BY TTID, BreakEnd, PunchIn, PunchOut, DateAdd("n", DateDiff("n",
TimeValue(CDate(0)), [BreakEnd]), DateAdd("d",-1,DateValue(PunchIn)))
HAVING DateAdd("n", DateDiff("n", TimeValue(CDate(0)), [BreakEnd]),
DateAdd("d",-1,DateValue(PunchIn))) Between [PunchIn] And [PunchOut];

I'm not sure about how to exclude weekend hours using just SQL in a
simple way. Perhaps the following VBA will give you some ideas:

http://groups.google.com/group/comp.databases.ms-access/msg/548d33651b087c05


James A. Fortune
(e-mail address removed)

SWEEPER - A train that stops at all stations. -- Dictionary of
Australian Slang, Second Edition, Sidney J. Baker, 1943 (Price: Three
shillings & sixpence)

Note that you can exclude the breaks that show up on weekends with the
WHERE clause in the following SQL:

qryBreaks:
SELECT TTID, BreakEnd, PunchIn, PunchOut
FROM tblTimeTickets, tblBreaks, tblI WHERE
Weekday(DateAdd("d",-1,DateValue(PunchIn)), 2) < 6
GROUP BY TTID, BreakEnd, PunchIn, PunchOut,
DateAdd("d",-1,DateValue(PunchIn))+[BreakEnd]
HAVING (((DateAdd("d",-1,DateValue(PunchIn))+[BreakEnd]) Between
[PunchIn] And [PunchOut]));

You're still left with the problem of not counting the minutes in
weekend days with DateDiff("n", PunchIn, PunchOut). It would sure be
nice to subtract CountWeekendDays(PunchIn, PunchOut) * 24 * 60 if no one
can punch in or out on a weekend, but that would use VBA. Maybe I
should combine the parts of the VBA for the CountWeekendDays function
into one long, ugly expression that can be used in SQL or build a big
table with just weekend dates . I'll try to give it some more thought.

James A. Fortune
(e-mail address removed)

AFTERGAME, COME THE - To make an assertion of the "I-knew-it" or
"Didn't-I-tell-you-so?" kind. -- Dictionary of Australian Slang, Second
Edition, Sidney J. Baker, 1943 (Price: Three shillings & sixpence)

 

[Ken Sheridan]

A further thought: if you wish to exclude public or concessionary holidays
from the computation then create a table Holidays with a column HolDate of
date/time data type and amend the function like so:

Public Function TimeWorked(dtmJobStart As Date, _
dtmJobFinish As Date, _
dtmDayStart As Date, _
dtmDayFinish As Date) As Double


Dim dtmDate As Date
Dim lngMinutes As Long
Dim lngMinutesInDay As Long
Dim strCriteria As String

lngMinutesInDay = DateDiff("n", dtmDayStart, dtmDayFinish)

For dtmDate = DateValue(dtmJobStart) To DateValue(dtmJobFinish)
strCriteria = "Holdate = #" & Format(dtmDate, "yyyy-mm-dd") & "#"
If Weekday(dtmDate, vbMonday) < 6 And _
IsNull(DLookup("HolDate", "Holidays", strCriteria)) Then
If dtmDate = DateValue(dtmJobStart) Then
If TimeValue(dtmJobStart) > dtmDayStart Then
lngMinutes = _
DateDiff("n", TimeValue(dtmJobStart), dtmDayFinish)
Else
lngMinutes = lngMinutesInDay
End If
ElseIf dtmDate = DateValue(dtmJobFinish) Then
If TimeValue(dtmJobFinish) < dtmDayFinish Then
lngMinutes = lngMinutes + _
DateDiff("n", dtmDayStart, TimeValue(dtmJobFinish))
Else
lngMinutes = lngMinutes + lngMinutesInDay
End If
Else
lngMinutes = lngMinutes + lngMinutesInDay
End If
End If
Next dtmDate

TimeWorked = lngMinutes / 60

End Function

Ken Sheridan
Stafford, England

 

[Kevo]

Good Morning Ken,
sorry it took so long to reply but I was out of
the office until now.
I would like to thank you for taking the time to read my post and posting
your great instructions. Very clear and easy to follow.
I have just ran the query and it works fantastically well.
Next time you are in Manchester, a pint (or a Jack Daniels) will be waiting.
Thanks again, Kevin Jackson

It would be difficult to do this without using VBA; it would need an
expression of Byzantine complexity. A VBA function is a lot easier. The
following function should do it. All you need to do is paste it into a
standard module in your database:


Public Function TimeWorked(dtmJobStart As Date, _
dtmJobFinish As Date, _
dtmDayStart As Date, _
dtmDayFinish As Date) As Double


Dim dtmDate As Date
Dim lngMinutes As Long
Dim lngMinutesInDay As Long

lngMinutesInDay = DateDiff("n", dtmDayStart, dtmDayFinish)

For dtmDate = DateValue(dtmJobStart) To DateValue(dtmJobFinish)
If Weekday(dtmDate, vbMonday) < 6 Then
If dtmDate = DateValue(dtmJobStart) Then
If TimeValue(dtmJobStart) > dtmDayStart Then
lngMinutes = DateDiff("n", TimeValue(dtmJobStart),
dtmDayFinish)
Else
lngMinutes = lngMinutesInDay
End If
ElseIf dtmDate = DateValue(dtmJobFinish) Then
If TimeValue(dtmJobFinish) < dtmDayFinish Then
lngMinutes = lngMinutes + _
DateDiff("n", dtmDayStart, TimeValue(dtmJobFinish))
Else
lngMinutes = lngMinutes + lngMinutesInDay
End If
Else
lngMinutes = lngMinutes + lngMinutesInDay
End If
End If
Next dtmDate

TimeWorked = lngMinutes / 60

End Function


Make sure you save the module with a different name to the function, e.g.
mdlTimeStuff.

You can then call it, passing the start and end date/time values for the job
and the start and end times for the working day, e.g. in the 'field' row of a
query in design view (all as one line)

HoursWorked:TimeWorked([Available date], [Despatch date],
#06:00:00#,#20:00:00#)

If you try it in the debug window, again as a single line, with your sample
values:

? TimeWorked(#2008-04-02 07:40:14#, #2008-04-03
09:15:21#,#06:00:00#,#20:00:00#)

This returns 15.5833333333333.

Note that date literals must not be in the UK short date format of
dd/mm/yyyy. The US format of mm/dd/yyyy can be used, but I prefer the
unambiguous ISO standard date format of YYYY-MM-DD.

The function does assume continuous time on each day from start to finish,
and does not allow for break time. It also does not allow for overtime if
work starts before 06.00 or finishes after 20.00 on any of the days. It
does, however, exclude weekends from the computation.

Ken Sheridan
Stafford, England

I am fairly new to Access and cannot use VBA.
I normally use Access to work with quantities only, but I am now trying to
calculate time differences and have become stuck.
I am trying to work out the "actual" time it takes to complete an order.
Using the expression

ateDiff("n",[Available date],[Despatch date])/60

But this doesn't take in to consideration our Working days (Monday to
Friday) or working times (06:00 to 20:00).

Say [Available date] = 02/04/2008 07:40:14
and [Despatch date] = 03/04/2008 09:15:21
I get the answer as 25.6 hours. But the "actual" time is only 15.6 hours.
Some orders can take 5 or 6 days to complete.

I need to calculate the total hours actually worked to complete the order

Can anybody please help?
Best Regards,
Kevo

 

[James A. Fortune]

Good Morning Ken,
sorry it took so long to reply but I was out of
the office until now.
I would like to thank you for taking the time to read my post and posting
your great instructions. Very clear and easy to follow.
I have just ran the query and it works fantastically well.
Next time you are in Manchester, a pint (or a Jack Daniels) will be waiting.
Thanks again, Kevin Jackson

I'm glad Ken's solution worked for you Kevo. I liked Ken's solution
better too, but that's bad of me. It's hard for me to push the more
elegant code solutions when I agree with Ken that separate business
rules are worth considering. For holidays, I use holiday functions and
reserve the possibility of using a holiday table on top of that as a
sweeper. BTW, the holiday functions correspond to elegant code , and
the holiday table corresponds to separate business rules. For your
problem, even though my SQL solution was less elegant, it corresponds to
separate business rules, while Ken's solution corresponds to elegant
code. Although the need for separate business rules really depends on
the customer, I will probably gravitate toward a hybrid of the two
ideas. I.e., use elegant code whenever possible, yet have enough
separate business rules to have the customer feel empowered. I will
likely never use the time filter I described because of the vagaries of
time tickets and shifts, but I found it to be an interesting exercise.
Holidays sometimes have their own quirks too. By separate business
rules in Access I mean a table, but I will also be looking at the
possibility of creating a separate business layer in the future.

James A. Fortune
(e-mail address removed)

TURN AGAINST THE HAND - (Ploughing term.) In steep country it may be
inadvisable or impossible to turn the furrow uphill. When this is done
the teamster is said to be "turning against the hand." -- Dictionary of
Australian Slang, Second Edition, Sidney J. Baker, 1943 (Price: Three
shillings & sixpence)