AnandSahoo
asked on
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});//
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)
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});//
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)
ASKER
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
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
Did you comment those lines out?
mlmcc
mlmcc
ASKER
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.
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.
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
If StartTime < SetStart then
StartTime := SetStart;
If EndTime < SetStart then
EndTime := SetStart;
Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600);
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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
James
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
mlmcc
ASKER
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
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
ASKER
Closing now. In case find any error again,will get back
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