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

subhanak

Board Regular
Joined
Jul 9, 2005
Messages
75
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.

StartDateEndDate
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
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
What about holidays? Should they be excluded also?
What have you tried?
 
Upvote 0
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() ?
 
Upvote 0
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 DateEnd DateHours (Excl weekend)
06/12/2020 00:00:0006/13/2020 19:00:0019 Hrs
06/12/2020 11:00:0006/15/2020 09:00:0020 Hrs
06/13/2020 00:00:0006/14/2020 19:00:000 Hrs

Any sugesstions? please.

Thanks,
Ben
 
Upvote 0
? 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?
 
Upvote 0
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
 
Upvote 0
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.

VBA Code:
SELECT TblBEN.ID
, TblBEN.StartDate
, TblBEN.EndDate
, DateDiff("h",[StartDate],[Enddate]) AS Diff
FROM TblBEN;
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top