Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-05-11
12
Medium Priority
?
713 Views
Last Modified: 2012-06-28
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
Comment
Question by:AnandSahoo
  • 6
  • 4
  • 2
12 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 35741842
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

by:AnandSahoo
ID: 35741905
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 101

Expert Comment

by:mlmcc
ID: 35741971
Did you comment those lines out?

mlmcc
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:AnandSahoo
ID: 35753275
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35754946
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 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 35754976
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 35

Expert Comment

by:James0628
ID: 35759810
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 35760339
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 35

Expert Comment

by:James0628
ID: 35776030
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 101

Expert Comment

by:mlmcc
ID: 35777829
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

by:AnandSahoo
ID: 35807642
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
 

Author Closing Comment

by:AnandSahoo
ID: 35866078
Closing now. In case find any error again,will get back
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question