Back to working days / hours with holidays

[DajOne]

I am trying to get the working hours with holidays and weekend removed between 2 dates.

The code I am trying comes from this forum and users had success with it however I do not as holidays and weekends are not removed.

I have atached a test database. Can someone pointme in the right direction?

Thanks

 

[ajetrumpet]

Daj,

Honestly, I don't want to sift through your code, but I'm pretty sure my attachment will help you. It is a calculation of business days (that is, dates that do not fall on the specified holidays that I have chosen). You can see the method I used in the module. I have noted the dates that are excluded above each block of code. Let me know if this stuff is even relevant to your case.

I'm not even sure what you are trying to do here...weather it be calculating the hours that ARE part of the weekends/holidays, or if you're trying to calculate hours OUTSIDE of the weekends/holidays....???

 

[DajOne]

Thanks for the attachment I will take a close look. As for your question, I am trying to calculate the number of working hours between 2 general formatted dates fields. The results should not account for weekends, non working hours, and specific holidays.

 

[gemma-the-husky]

simple to understand whats happening, from basic principles

you need code that does something like

for checkday from startday to endday
if normalday(checkday) then etc
if weekendday(checkday) then etc
if holidayday(checkday) then etc
next checkday

so you can check for normalday, and weekendday by

the weekday(checkday) function which returns numbers, but is more easily interpreted by constants ie

if weekday(checkday)=vbmonday then etc
if weekday(checkday)=vbsunday then etc

so the problem is with holidayday (bank holiday?/annual holiday?) and you will need a table of specific dates to check your data against, since holidays vary from country to country

so effectively you are getting

if dateinholidaytable(checkday) then etc

 

[ajetrumpet]

Thanks for the attachment I will take a close look. As for your question, I am trying to calculate the number of working hours between 2 general formatted dates fields. The results should not account for weekends, non working hours, and specific holidays.

Well, that sample will help you quite a bit. At least it will give you some idea of how to avoid those specific dates you have mentioned. I would assume for something like this, you would need use DateDiff() with the hours interval, nested inside some code that looks similar to what is in the sample function...

 

[DajOne]

I went through the code with a lens and cannot figure it out. Hired a programmer to help me out.. thanks all..

 

[dejones923]

Backing out Weekend Days

This is what I used to back out the weekend days between two different dates in determining lagtime; basically by using DateDiff function.