Solved

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

Posted on 2011-05-11
709 Views
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
Question by:AnandSahoo

LVL 100

Expert Comment

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 Comment

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

LVL 100

Expert Comment

Did you comment those lines out?

mlmcc
0

Author Comment

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

LVL 100

Expert Comment

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

LVL 100

Accepted Solution

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

LVL 34

Expert Comment

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

LVL 100

Expert Comment

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

LVL 34

Expert Comment

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

LVL 100

Expert Comment

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 Comment

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 Closing Comment

Closing now. In case find any error again,will get back
0

## Featured Post

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
how to add IIS SMTP to handle application/Scanner relays into office 365.
This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.