Date and Time Difference in MS Access 2007

[Analyst1982]

Dear Friends,

I have an excel sheet which contains networking days formula, I want to migrate from excel to access. I am not sure how I will calculate this in access. Kindlly note that I have to exclude weekend (i.e. Friday and Saturday) and need to calculate total hours taken to handle a request (working hours 7:15 AM - 2:30 PM)


Req_Date_Time
Response_Date_Time
Total_Time_Taken

I would really appreciate your usual expert advise, if possible share a dummy database which contains these 3 fields and total time taken is calculated.

Thanks.

 

[Micron]

I think you will need a function - see if you can use the code below. The following assumes:
- each day the task is incomplete, that is a duration of 7.25 hours.
- Sunday is the first day of the week in your regional settings
- you only wanted the number of hours based on the work days, having said nothing about partial days (i.e. a task that starts at 2:00 PM one day and finishes at 10:00 AM on the last day). If that is a concern, you would need to used the DateDiff function to get the total hours in the span and subtract 16.75 for each day that aryWrkDays > 0 AND subtract 24 hours for each day that aryWrkDays = 0.
Also, Response and ReqDate paraphrase your field names (I'm too lazy to bother with underscores and long names ).
For testing, I hard coded dates for my variables. You can remove the variables and their values (red part) and replace the references in code (blue) to the names of your fields. Somewhere (I have no idea where based on the limited information you supplied) you could call this function using Total_Time_Taken = calcDiff. If you have the dates available at this point, it can be changed to
Total_Time_Taken = calcDiff [Req_Date_Time], [Response_Date_Time] but the first line in the function would have to be
Public Function calcDiff(Req_Date_Time As Date, Response_Date_Time As Date) As Single

Public Function calcDiff() As Single
Dim daySpan As Integer, n As Integer
Dim aryWrkDays() As Integer
Dim i As Single 'i represents a whole workday

[COLOR=#ff0000]'***********create and set variables for testing; to be removed
Dim Response As Date, ReqDate As Date
Response = #5/17/2016#
ReqDate = #5/1/2016#
'***********[/COLOR]
daySpan = DatePart("d", [COLOR=#0000cd]Response[/COLOR]) - DatePart("d", [COLOR=#0000cd]ReqDate[/COLOR]) + 1 'get # of days
ReDim aryWrkDays(daySpan) 'set array size to span value
For n = 0 To daySpan - 1 'minus 1 because array is zero based
  If Weekday([COLOR=#0000cd]ReqDate [/COLOR]+ n) > 0 And Weekday([COLOR=#0000cd]ReqDate [/COLOR]+ n) < 6 Then 'if between Sun. and Thurs. inclusive...
'  Debug.Print [COLOR=#0000cd]ReqDate [/COLOR]+ n
    aryWrkDays(n) = DatePart("d", ([COLOR=#0000cd]ReqDate [/COLOR]+ n))'...add it to array in 'n' positon
  End If
Next

For n = 0 To daySpan -1
'Debug.Print aryWrkDays(n)
    If aryWrkDays(n) > 0 Then i = i + 7.25 'zeros=Fri. & Sat., all others are day of week so add 7.25 hrs.
Next

calcDiff = i 'or 

End Function

 

[dhen21dx]

Hi,

I do also have same problem but more complex than this. Can you also help me, i need a logic that, let say i have fields that have received and fixed date, but i need to know which record are received from <8:00 to 15:00 then i need to check whether it fixed at 8:00 to 17:00, otherwise calculate the time if within 24 hours, this excludes Sat and Sun. If fixed on Sat or Sun, these will be 0(to be add on the time computed in working days).

Thanks.

 

[Micron]

First, you need to start your own thread for your own issue. What you're doing here is called "hijacking" the thread.
When you do, be very clear. I think I get most of it, but not "if within 24 hours". Of what? (please do not answer that question or elaborate in this thread).