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

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
WhileReadingRecords;
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.
BeforeReadingRecords;
DateVar Array Holidays := [
Date (2003,12,25),
Date (2003,12,31)
];
0

Calculate Hours - 2nd Formula
WhileReadingRecords;
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
Asked:
AnandSahoo
  • 6
  • 4
  • 2
1 Solution
 
mlmccCommented:
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
 
AnandSahooAuthor 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
 
mlmccCommented:
Did you comment those lines out?

mlmcc
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
AnandSahooAuthor 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.
Request your help on this.
0
 
mlmccCommented:
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
 
mlmccCommented:
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
 
James0628Commented:
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:

http://www.kenhamady.com/form01.shtml

http://www.kenhamady.com/form13.shtml


 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
 
mlmccCommented:
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
 
James0628Commented:
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
 
mlmccCommented:
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
 
AnandSahooAuthor 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

Thanks for your timely help
0
 
AnandSahooAuthor 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now