How to calculate two different dates and time in access and excluding weekend

[lh2017]

Hello All,

I am just starting out using Access 2013 (long time excel user). I am trying to figure out how to calculate two different dates and time in access and excluding weekend. I have read many forums and research a couple days now and still do not understand how to get to the result. I have use the below DateDiff statement but cannot figure how to exclude weekends. I greatly appreciate anyone helping out. Thanks!

ResolutionTimeToClose: DateDiff('h',[ClosedOn],[ReportedOn])

[Bulzie]

So DateDiff gives you a numeric value for days or hours between those dates, does not give you a date to say if a weekend or not. What date are you trying to see if it is a weekend or not? Maybe give data examples of dates and what you are wanting to do.

[pbaldy]

Here are a couple of ways:

http://www.theaccessweb.com/datetime/date0006.htm

[lh2017]

I get the result in hours diff. which is what I wanted but I do not want to count weekend hours (48 hrs for one weekend time diff between two dates). As starting out, I only want hours difference between two dates (excluding weekends) but as I get more into later and understand it more, I will incorporate only count hours diff. in work hours only and also excluding holidays. Thanks!

Expr1	ReportedOn	ClosedOn
-1	5/1/2017	5/1/2017
-20	5/10/2017	5/11/2017
-19	5/10/2017	5/11/2017
-96	5/11/2017	5/15/2017

[ArviLaanemets]

No easy way to do this using directly formulas, especially when you want holidays (and maybe some workdays preceeding holidays with shortened worktime to boot) take in account too.

I use in such case a calendary table, wih all dates listed starting from some date, and regularily updated (p.e. on open) by procedure.
Along with dates are calculated:
1. weekday numbers;
2. holidays;
3. is the workday shortened or not;
4. number of working hours for every workday;
5. in case there is the need to calculate the time difference between datetimes (date + time), the start and end of worktime (in case there are shifts, or employees having different working shedules, this part will be more complicated).

Having such file with dates prepared until some reasonable timeline in future, you can easily calculate what you want.

[Bulzie]

Looks like what Paul linked should do what you need also, 2nd part excludes Holidays I believe.

[lh2017]

Looks like what Paul linked should do what you need also, 2nd part excludes Holidays I believe.

I have no idea where to begin with Paul's code. Where exactly do I input these code? Is it in SQL view? in Design View, etc?

This is what I have in my SQL view:

SELECT DateDiff('h',[ClosedOn],[ReportedOn]) AS Expr1, *
FROM eRequestfinaldatabase
WHERE (((Month([eRequestfinaldatabase].[ReportedOn]))=5))
ORDER BY eRequestfinaldatabase.ReportedOn;

So do I delete this enter this below. I know I am definitely wrong but just to show how clueless I am in access:

On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

[pbaldy]

If you want hours, I don't think the link I posted does it. Perhaps:

https://www.accessforums.net/showthr...business+hours

In any case, this type of thing would be done with a public function. You'd paste the code into a standard module, which would let you call it from anywhere you needed the result.

[Bulzie]

Are all your days 8 hour work days or do you really want 24 hours a day? You could do the DateDiff in days to get a total days, then run code to see if any holiday day (maybe table of holiday dates as mentioned already) falls within those 2 dates and if so, subtract 1 day for each holiday day. At the end you multiple the number of days left times 8 to get working hours between those dates minus holiday days. Again Paul's code might do this already, just looking to clarify what you are needing.

If you also need to exclude weekends, you could also do it where you start with first date and check if it is holiday or weekend, if not add 8 hours to total, check next date for same until you get to last date. But again, Paul's code might already do this so check first before you reinvent the wheel.