I need to calculate hours between a start date/time and an end date/time excluding saturday & sunday hours.
The start may possibly start on a saturday or sunday and the end may possibly end on a saturday or sunday.
But i only want the sum of weekday hours elapsed.
eg 3/jun/2011 11:00:00 am to 8/jun/2011 9:00:00 am would = 70 hours
eg 4/jun/2011 11:00:00 am to 8/jun/2011 9:00:00 am would = 57 hours
eg 3/jun/2011 11:00:00 am to 5/jun/2011 9:00:00 am would = 13 hours
Any help would be appreciated
Last edited by wotadude; 06-09-2011 at 04:40 PM.
Do you absolutely need VBA?
This formula will give you the required hours, whether start/end dates are at weekends or not
=NETWORKDAYS(A2,B2)+NETWORKDAYS(B2,B2)*(MOD(B2,1)-1)-NETWORKDAYS(A2,A2)*MOD(A2,1)
where A2 is start date/time and B2 is end date/time
format result cell as [h]:mm
Audere est facere
Thats a good start.
But yes i do require it in vba, so i can reuse in an add-in.
What i want ultimatley is the difference in decimal workdays.
Are you able to help ?
So if you want decimal workdays then instead of 70 hours that would be 2.92 days? If so then that formula will give you exactly that result, you just format the result cell as number rather than time format.
VBA isn't my forte - I'll see if anybody else can help.......
This isn't quite right, as I get a result of 3 rather than 2.92, but I can't see what needs tweaking. Anyway, for more pairs of eyes ...
A subroutine might look like this:
Please Login or Register to view this content.
and an equivalent function would look like this:
Please Login or Register to view this content.
I'll keep looking in case it hits me but, any suggestions welcome.
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Stepping through the function, I get these results:
Please Login or Register to view this content.
Whereas, the formula, broken down, gives:
Please Login or Register to view this content.
I certainly can't improve on DLL's formula, so,
Please Login or Register to view this content.
Last edited by shg; 06-09-2011 at 11:05 AM. Reason: change Dates to Doubles -- fewer conversions
Entia non sunt multiplicanda sine necessitate
Thats a beautiful thing.
Just what i needed.
Thanks to all ........
Hey guys,
I'm rehashing this thread because it is the closest thing to what I need to do. I would like to do essentially the same thing as wotadude (exclude weekends), but also exclude the hours that we are not open (we are open from 7am to 8 pm from M-F).
The only problem is that in the columns that describe the times and dates of the open and the close, the time and the date are both in there. It looks like this (6/7/2013 5:50:00).
So the real question is, would it be possible to determine the amount of hours that have passed when we exclude times closed and weekends when the date is in that format?
Thanks everyone, much obliged.
Hello VbaNewbieMan,
Can you please post your question as a new thread - if this thread (or any other) is relevant just include a link, thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks