MS Access (VBA) : Calculating the exact elapsed hours between two dates excluding weekends

[subhanak]

Hi,
I have an access table that includes fulfillment dates/time related to orders. I would like to calculate the exact elapsed hours between two dates excluding weekends. is there anyway to achieve such calculation ?
Please let me know.

StartDate	EndDate
06/13/2020 19:12:08​	06/14/2020 15:12:08​
06/12/2020 11:47:46​	06/15/2020 09:47:46​
06/11/2020 00:00:00​	06/16/2020 23:59:59​
06/11/2020 02:12:08​	06/11/2020 19:12:08​
06/14/2020 00:12:08​	06/15/2020 19:12:08​
06/12/2020 00:12:08​	06/13/2020 19:12:08​

Your assistance is appreciated.
Thanks,
Ben

 

[jackd]

What about holidays? Should they be excluded also?
What have you tried?

 

[welshgasman]

How about just finding out how many weekends within the dates and then subtract that number of days * 24 from the basic calculation with DateDiff() ?

 

[subhanak]

Hi,

Thank you for your response.The holidays are not a consern.
I would like to calculate the exact elapsed hours excluding weekend hours. I could not find a way to use the datediff() function.
I tried few options like slicing the target range through an if statement, but so far no luck!

Also, I am using MS Access not Excel.

Start Date	End Date	Hours (Excl weekend)
06/12/2020 00:00:00	06/13/2020 19:00:00	19 Hrs
06/12/2020 11:00:00	06/15/2020 09:00:00	20 Hrs
06/13/2020 00:00:00	06/14/2020 19:00:00	0 Hrs

Any sugesstions? please.

Thanks,
Ben

 

[welshgasman]

? datediff("h",#06/12/2020 00:00:00#, #06/13/2020 19:00:00#) produces 43 ? 24 + 19

Try the other values

However as 13th is a weekend, shouldn't that just be 24?

 

[subhanak]

Sorry, you are right. For that given entry it should be 24 Hrs. My apology,

Thanks,
Ben

 

[welshgasman]

It should not be 24, because I would like to track the exact elapsed hours, therefore, it is 19 hours.

Thnaks,
Ben

Ok, thoroughly confused. You have 24 hours for the Friday 12th. Saturday 13th is not meant to be included?,

Are we losing something in translation? '
Calculating the exact elapsed hours between two dates excluding weekends

 

[jackd]

I don't think you an do this with Datediff only. You'll have to iterate over the days (start to end) and ignore those that are Weekend days (Sat/Sun).
This will get the hours between start and end, but does not account for weekend days.

SELECT TblBEN.ID
, TblBEN.StartDate
, TblBEN.EndDate
, DateDiff("h",[StartDate],[Enddate]) AS Diff
FROM TblBEN;

 

[subhanak]

Thank you for your response. I am familiar with the Datediff() function, but it would not get me the result that I am looking for.
Most likely, the desired result can be achieved through a VBA function, but I am not familiar with this language.
Thank you for your assistance.

 

[welshgasman]

I would be creating a function that did pretty much what I first posted?
However it would not calculate values as you seem to think they should be?, so i will bow out.