• Status: Solved
• Priority: Medium
• Security: Public
• Views: 717

# How to exclude non business hours and Weekends from 2 date values

I have asked this question before and got 2 solutions attached.
Both the formulas giving too many errors. Can someone help me with this please.
I need to exclude non business hours and weekends from 2 dates

Below is the formula given. When I tried for date 18/04/2011 06:00:10 and 28/04/2011 05:59:53
by taking business hours from 7 to 22 its giving me 90 hours but it should be 120 hours.
Like this in many cases getting error.
Below is teh formula

Main formula
Local DateVar Start := {StartDate};   // place your Starting Date here
Local DateVar End := {EndDate};  // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0)  +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar 1;
For a := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays[a] ) in 2 to 6 and
Holidays[a] in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

//Holiday Listing formula to go into the report header of the report.
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0

Calculate Hours - 2nd Formula
NumberVar Days := {@Main Formual};  // The field that calculates your business days
TimeVar SetStart := TimeValue( "7:00");      // The start your work day
TimeVar SetEnd   := TimeValue("22:00");      // The end your work day
TimeVar StartTime := TimeValue({Start.Time});// The data field that holds your Start Time
TimeVar EndTime   := TimeValue({End.Time});  // The data field that holds your End Time

//These lines are only needed if your times are strings that do not indicate AM or PM, like "3:30"
//They will convert afternoon times to PM.  Of course, this won't work if your workday is over 12 hours.
If StartTime < (SetEnd - 43200) then StartTime := StartTime + 43200;
If EndTime   < (SetEnd - 43200) then EndTime   := EndTime   + 43200;

Days * ((SetEnd - SetStart) / 3600)
-  ((SetEnd     - EndTime)  / 3600)
-  ((StartTime - SetStart)  / 3600)

0
AnandSahoo
• 6
• 4
• 2
1 Solution

Commented:
Have you closed the other questions?
If so why if the formulas are giving errors?

What errors are you getting?
Is it just the wrong result?

Did you read these lines in the formula
//These lines are only needed if your times are strings that do not indicate AM or PM, like "3:30"
//They will convert afternoon times to PM.  Of course, this won't work if your workday is over 12 hours.
You have a 15 hour day so the formula doesn't work correctly.

Are the times in 24 hour notation?

mlmcc
0

Author Commented:
Hi Mlmcc

I am getting the answer as 90 Hours but If I do a manual calculation it is 120 hours

The date format is not 24 hours. AM and PM is there at the end.Hence I have iognored those 2 lines.
Can u advise how to overcome this If I have 15hour day
0

Commented:
Did you comment those lines out?

mlmcc
0

Author Commented:
Yes, I have commented it in actual formula.
The time taken to test is 18/04/2011 06:00:10 and 28/04/2011 05:59:53
Actual time if we excude the non business hours it is 120 hours
15 hours for 8 days. But in report it is coming as 90 hours.
0

Commented:
There seems to be an issue if your start or end time is outside the normal day.  Try this

If StartTime < SetStart then
StartTime := SetStart;
If EndTime < SetStart then
EndTime := SetStart;

Days * ((SetEnd - SetStart) / 3600)
-  ((SetEnd     - EndTime)  / 3600)
-  ((StartTime - SetStart)  / 3600);

mlmcc
0

Commented:
You may also need to handle the after hours issue

If StartTime < SetStart then
StartTime := SetStart;
If EndTime < SetStart then
EndTime := SetStart;

If StartTime > SetEnd then
StartTime := SetEnd;
If EndTime > SetEnd then
EndTime := SetEnd;

Days * ((SetEnd - SetStart) / 3600)
-  ((SetEnd     - EndTime)  / 3600)
-  ((StartTime - SetStart)  / 3600);

mlmcc
0

Commented:
I think mlmcc is correct and the problem is because the start and end time (around 6 AM) are outside of your working day (7 AM - 10 PM).  If you didn't already know, those formulas came from the following two Web pages:

If you'll notice, on the second page (with the hours formula), he says:

> You will get unpredicatble results if you have records that start and end 'after hours'.

mlmcc's adjustments may fix the problem.  I'm not entirely sure.  If not, you should be able to handle it by adjusting the starting and ending date and time.

One other thing:
Your holiday formula has dates in 2003.  They're actually just the dates from the formula on the first Web page above.  This is obviously not going to work for your test dates in 2011.  If you didn't realize this, the holiday dates need to be in the same year(s) as the dates in your data.  If the dates in your data could include multiple years (eg. 2010 and 2011), you need to include holiday dates for each of those years.

James
0

Commented:
Good point on the hoilday dates James.

I ran several tests to see what happened if the times used were before or after hours.  When I used the fix the answer was correct.  Using time outside resulted in various differences either more or less hours.

mlmcc
0

Commented:
Yeah, I did a couple of tests and your fix seemed to work, but I wasn't sure if it would handle everything or I just happened to pick some lucky examples.  I can't see anything wrong with it, but it seems too "easy" somehow.

James
0

Commented:
You are right it seems to easy.  Especially to have been missed when building the original formula to not handle the error of times outside normal working hours.

mlmcc
0

Author Commented:
Hi Mlmcc,

I am not able to reproduce any error though, But I understand there are some which I have seen al;ready

Give me few more days and will add my comment

0

Author Commented:
Closing now. In case find any error again,will get back
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.