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:

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,311
Office Version
  1. 365
What about holidays? Should they be excluded also?
What have you tried?
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
883
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

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

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
883

ADVERTISEMENT

? 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

Board Regular
Joined
Jul 9, 2005
Messages
75
Sorry, you are right. For that given entry it should be 24 Hrs. My apology,

Thanks,
Ben
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
883

ADVERTISEMENT

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

Well-known Member
Joined
Oct 19, 2006
Messages
1,311
Office Version
  1. 365
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;
 

subhanak

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

Well-known Member
Joined
May 25, 2013
Messages
883
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,905
Messages
5,627,568
Members
416,254
Latest member
Monicamicaela

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
Top