Solved

Calculate the difference between two date/time fields in MS Access

Posted on 2011-03-14
11
1,114 Views
Last Modified: 2012-08-13
I am having some difficulty calculating the difference between two date/time fields in a form.  The issue i have is calculating the difference using the "DateDiff" function, excluding weekends and only counting hours worked between 8:00 AM - 6:00 PM.  A holiday table is an option but not a necessity.  Appreciate your assistance.
0
Comment
Question by:kev_hinds
11 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35130286
What information are you trying to display in the form (something like 8.5 hours)?

Part of your challenge will be dealing with your 8:00 AM to 6:00 PM issue.  Assuming you have fields [Time In] and [Time Out] and you want to compute the difference between these values, but only within the bounds of the 8AM-6PM, you will first need to identify which date/time values to use.  I do this with a couple of general purpose functions (below) named fnMin() and fnMax().  These functions return the minimum and maximum values from among an array of parameters passed to them.  So the way I would do this is something like:

lngDurationMin = DateDiff("n", fnMax([Time In], #08:00#), fnMin(#18:00#, [Time Out]))

You can then convert the minutes to hours and minutes to display that information any way you want.

Public Function fnMin(ParamArray ValList() As Variant) As Variant

   Dim intLoop As Integer
   Dim myVal As Variant
   
   For intLoop = LBound(ValList) To UBound(ValList)
      If Not IsNull(ValList(intLoop)) Then
         If IsEmpty(myVal) Then
            myVal = ValList(intLoop)
         ElseIf ValList(intLoop) < myVal Then
            myVal = ValList(intLoop)
         End If
      End If
   Next
   fnMin = myVal
   
End Function
Public Function fnMax(ParamArray ValList() As Variant) As Variant

   Dim intLoop As Integer
   Dim myVal As Variant
   
   For intLoop = LBound(ValList) To UBound(ValList)
      If Not IsNull(ValList(intLoop)) Then
         If IsEmpty(myVal) Then
            myVal = ValList(intLoop)
         ElseIf ValList(intLoop) > myVal Then
            myVal = ValList(intLoop)
         End If
      End If
   Next
   fnMax = myVal
   
End Function

Open in new window

0
 
LVL 2

Expert Comment

by:sihar86
ID: 35130294
http://msdn.microsoft.com/en-us/library/Aa262712

if you want second format, you may try this
DateDiff('s',[date1],[date2]) AS Expr1

Open in new window

0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 35130465
kev_hinds,

You may want to see the function I posted to this previous question:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24445755.html

It allows you to specify what the "normal business hours" are, what weekdays are considered "business days", and also allows you to have a holidays table to do a holiday override.

Patrick
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35130505
Patrick,

You are such an overachiever.   ;-)

Dale
0
 

Author Comment

by:kev_hinds
ID: 35130512
@ fved - I inserted your modules in my afterupdate function for the date returned however it gives me a large negative number - I changed the "n" to a "h" as i need my output in hours - preferably in whole numbers however the number is still negative

the format is as follows:
date received: 2/18/2010 9:01:00 AM
date returned: 2/19/2010 11:35:00 AM

Expected result: 13 hours
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:kev_hinds
ID: 35130616
@matthewspatrick: Thanks! Works perfectly!!! Appreciated.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 35130636
You can use the function below.
To obtain a rounded count of hours, use:

intHours = Val(Format(ISO_WorkTimeDiff(#2/18/2010 9:01:00 AM#, #2/19/2010 11:35:00 AM#, False) / 60, "0"))

/gustav

Public Function ISO_WorkTimeDiff( _
  ByVal datDateTimeFrom As Date, _
  ByVal datDateTimeTo As Date, _
  Optional ByVal booNoHours As Boolean) _
  As Long

' Purpose: Calculate number of working minutes between date/times datDateTimeFrom and datDateTimeTo.
' Assumes: 5 or 6 working days per week. Weekend is (Saturday and) Sunday.
' Returns: "Working minutes". Divide by 60 to obtain working hours.
' Limitation: Does not count for public holidays.
'
' May be freely used and distributed.
' 2001-06-26. Gustav Brock, Cactus Data ApS, Copenhagen
'
' If booNoHours is True, time values are ignored.

  ' Specify begin and end time of daily working hours.
  Const cdatWorkTimeStart   As Date = #8:00:00 AM#
  Const cdatWorkTimeStop    As Date = #6:00:00 PM#
  Const cbytWorkdaysOfWeek  As Byte = 5

  Dim bytSunday             As Byte
  Dim intWeekdayDateFrom    As Integer
  Dim intWeekdayDateTo      As Integer
  Dim datTimeFrom           As Date
  Dim datTimeTo             As Date
  Dim lngDays               As Long
  Dim lngMinutes            As Long
  Dim lngWorkMinutesDaily   As Long
  
  ' No special error handling.
  On Error Resume Next
  
  If DateDiff("n", datDateTimeFrom, datDateTimeTo) <= 0 Then
    ' Nothing to do. Return zero.
  Else
    
    ' Calculate number of daily "working minutes".
    lngWorkMinutesDaily = DateDiff("n", cdatWorkTimeStart, cdatWorkTimeStop)
    
    ' Find ISO weekday for Sunday.
    bytSunday = WeekDay(vbSunday, vbMonday)
    
    ' Find weekdays for the dates.
    intWeekdayDateFrom = WeekDay(datDateTimeFrom, vbMonday)
    intWeekdayDateTo = WeekDay(datDateTimeTo, vbMonday)
    
    ' Compensate weekdays' value for non-working days (weekends).
    intWeekdayDateFrom = intWeekdayDateFrom + (intWeekdayDateFrom = bytSunday)
    intWeekdayDateTo = intWeekdayDateTo + (intWeekdayDateTo = bytSunday)
    
    ' Calculate number of working days between the weeks of the two dates.
    lngDays = (cbytWorkdaysOfWeek * DateDiff("w", datDateTimeFrom, datDateTimeTo, vbMonday, vbFirstFourDays))
    ' Add number of working days between the two weekdays, ignoring number of weeks.
    lngDays = lngDays + intWeekdayDateTo - intWeekdayDateFrom - (cbytWorkdaysOfWeek * (intWeekdayDateTo < intWeekdayDateFrom))
    
    If Not booNoHours = True Then
      ' Extract begin and stop hour (time) for the working period.
      datTimeFrom = TimeSerial(Hour(datDateTimeFrom), Minute(datDateTimeFrom), Second(datDateTimeFrom))
      datTimeTo = TimeSerial(Hour(datDateTimeTo), Minute(datDateTimeTo), Second(datDateTimeTo))
      ' Adjust times before or after daily working hours to boundaries of working hours.
      If DateDiff("n", datTimeFrom, cdatWorkTimeStart) > 0 Then
        datTimeFrom = cdatWorkTimeStart
      ElseIf DateDiff("n", datTimeFrom, cdatWorkTimeStop) < 0 Then
        datTimeFrom = cdatWorkTimeStop
      End If
      If DateDiff("n", datTimeTo, cdatWorkTimeStart) > 0 Then
        datTimeTo = cdatWorkTimeStart
      ElseIf DateDiff("n", datTimeTo, cdatWorkTimeStop) < 0 Then
        datTimeTo = cdatWorkTimeStop
      End If
      
      ' Calculate number of working minutes between the two days, ignoring number of days.
      lngMinutes = DateDiff("n", datTimeFrom, datTimeTo)
    End If
    
    ' Calculate number of working minutes between the two days using the workday count.
    lngMinutes = lngMinutes + (lngDays * lngWorkMinutesDaily)
  
  End If
  
  ISO_WorkTimeDiff = lngMinutes

End Function

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35130648
Kev,

You didn't mention that these values might span multiple days, bad assumption on my part.
0
 

Author Comment

by:kev_hinds
ID: 35130775
No problem fved ... i will try to be more succinct next time ... was my first question ... appreciate the prompt feedback though.  thanks.
0
 
LVL 18

Expert Comment

by:lludden
ID: 35130794
I would create a specific calendar table, with not only days, but hours.

Here is the SQL to create a calendar table with hours
DECLARE  @integers  TABLE (i integer)
insert into @integers (i) values (0)
insert into @integers (i) values (1)
insert into @integers (i) values (2)
insert into @integers (i) values (3)
insert into @integers (i) values (4)
insert into @integers (i) values (5)
insert into @integers (i) values (6)
insert into @integers (i) values (7)
insert into @integers (i) values (8)
insert into @integers (i) values (9)  

create table calendar
( date          datetime   not null primary key,
HourOfDay int,
DayofWeek int
)

declare @startdate datetime
set @startdate = '2011-01-01'
insert
  into calendar
select dateadd(hour,n,@startdate)
     , DATEPART(hour,dateadd(hour,n,@startdate))
     , DATEPART(WEEKDAY,dateadd(hour,n,@startdate))
  from (
       select g.i*100000+m.i*10000+k.i*1000+h.i*100+t.i*10+u.i  as n
         from @integers u
            , @integers t
            , @integers h
            , @integers k
            , @integers m
            , @integers g
       ) as numbers
 where n between 0 and 100000
order
    by n

Then you can query the table to get the number of hours between two times:
SELECT COUNT(*) FROM Calendar WHERE date between '2011-01-03 5:00' AND '2011-01-11 9:00' AND hourofday between 9 and 18 and dayofweek between 2 and 6

Then just add the minutes to and from the hour boundary.

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35130816
In that case, welcome to EE.  

It helps to be as specific as possible (without getting too long), to include sample data, so that the experts can actually try their solution with a couple of examples of your data.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Data type mismatch in criteria expression 2 26
MS Access to SQL Conversion - Bit and Yes/No Datatypes 12 46
Run Time Error 3075 15 44
Access Excel export not behaving 2 25
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now