Calc Business Hours when Start or End Date During Weekend

[VAJonD]

I am working on a report that calculates the number of hours necessary to resolve technical issues. Each ticket contains a Start and End Date/Time

I have used Ken Hamandys solution to calculate this value excluding Holidays and Weekends and it seems to work except -

The only issue that remains is if the ticket has a start of end date that falls on the weekend. In thoses case I get negative results.

Below is a look at the results. (normal tour is 7a-7p)

HD0000000187668 June 4, 2009 12:00 June 5, 2009 6:00 420
HD0000000187669 June 5, 2009 12:00 June 8, 2009 6:00 420
HD0000000187670 June 6, 2009 12:00 June 8, 2009 6:00 -300
HD0000000187671 June 5, 2009 12:00 June 6, 2009 6:00 -300
HD0000000187672 May 22, 2009 12:00 May 26, 2009 6:00 420
HD0000000187675 June 15, 2009 21:00 June 16, 2009 4:00 0

I thought that the code presented accounted for the fact that the record could fall on a weekend and adjusts for it here....

//Main Formula - Calculates Work Days - excludes weekends (Sat and Sun) and Holidays defined
WhileReadingRecords;
Local DateTimeVar Start := Date({HPD_HelpDesk.WIP Start});// Updated field
Local DateTimeVar End := Date({HPD_HelpDesk.WIP First Stamped});// Updated field
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+ <<<<< Here
(if DayOfWeek(Start)=1 then -1 else 0)+
(if DayOfWeek(End)=7 then -1 else 0);

Local NumberVar i;
For i:= 1 to Count(holidays)
do (if DayOfWeek (holidays) in 2 to 6 and
holidays in start to end then Hol:=Hol+1);

Weeks+Days-Hol

But alas it doesn't seem to figure it in and I'm getting the negative amounts.

Does anyone have any suggestions for me?

Thanks so much.

 

[lbass]

Your sample data shows date differences of between one and four days, so how are you arriving at 420 and -300 hours? Show us the calculation you are using to get there.

-LB

 

[VAJonD]

Thanks for the post lbass. I'm converting the hours to minutes towards the end of the calculation.

in the example

HD0000000187669 June 5, 2009 12:00 June 8, 2009 6:00 420

6/5 @ 12pm to 6/5 @ 7pm (end of tour) = 7 hours
6/8 @ 6am is before the beginning of the tour thus 0 hours.

7 hours converts to 420 minutes.

below it he calculation to convert hours to minutes.

//Formula to calculate the number of work hours between two DateTime values as calculated in the Work Days formula
WhileReadingRecords;
NumberVar Days:={@PickUp Work Days 3_13_09 Revised};//This field calculates your business days using work days formula
TimeVar SetStart:= TimeValue("7:00");
TimeVar SetEnd:= TimeValue("19:00");

TimeVar StartTime:=TimeValue({@Out of Tour Arrival});//The data field that holds the arrival time of ticket
TimeVar EndTime:=TimeValue({@Out of Tour Pick Up});//The data field that holds the WIP time of the ticket

If StartTime < (SetEnd - 43200) then StartTime := StartTime + 43200;
If EndTime < (SetEnd - 43200) then EndTime := EndTime + 43200;

Round (((Days*((SetEnd-SetStart)/3600) <<< I round and
-((SetEnd-EndTime)/3600) convert here.
-((StartTime-SetStart)/3600))*60),0)

 

[lbass]

First I think you should narrow down whether the workdays is returning the correct number and whether that number is always positive.

If that is correct, then I would also take a look at your {@Out of Tour Arrival} and {@Out of Tour Pickup} formulas to make sure that Arrival formula is always earlier than the Tour Pickup formula in your data.

-LB

 

[VAJonD]

Thanks again lbass....

My start date (WIP Started) will always be before my end date (WIP First Stamped) as far as the data goes.

My assumption is that this bit of code in the WORK DAYS formula from K. Hamandy would prevent it from producing a negative amount of days....

Days:=DayOfWeek(End)-DayOfWeek(Start)+1+
(if DayOfWeek(Start)=1 then -1 else 0)+
(if DayOfWeek(End)=7 then -1 else 0);

As far as my OUT OF TOUR ARRIVIAL and OUT OF TOUR PICKUP formulas I'm just testing to see if the hour is outside the 7-7 tour and forcing the date to the end/beginning of that day. As tickets that start and end in different days (as long as they don't start and end on a weekend) calculate correctly... again I am assuming that they are too working correctly.

OUT OF TOUR ARRIVAL
if hour({HPD_HelpDesk.WIP Start}) > 19 Then// Start Time is outside normal hours with updated fields
datetime(date({HPD_HelpDesk.WIP Start}),time(19,0,0))else
if hour({HPD_HelpDesk.WIP Start}) < 7 Then
datetime(date({HPD_HelpDesk.WIP Start}),time(7,0,0)) else
{HPD_HelpDesk.WIP Start};

OUT OF TOUR PICK UP
if hour({HPD_HelpDesk.WIP First Stamped}) > 19 Then// End Time is outside normal hours with updated fields
datetime(date({HPD_HelpDesk.WIP First Stamped}),time(19,0,0))else
if hour({HPD_HelpDesk.WIP First Stamped}) < 7 Then
datetime(date({HPD_HelpDesk.WIP First Stamped}),time(7,0,0)) else
{HPD_HelpDesk.WIP First Stamped};

I wondering if I add some code to my OUT OF TOUR formulas to dateadd days to the data fields if they fall on a DayOfWeek that is 1 or 7? Would that be the right place to stick that code or should it go in the DAYS forumla.

Thanks again for your help...as you can tell I'm kind of stuck in the mud and I feel like you are my whench.

 

[lbass]

I think you need to check your detail level data for each of these formulas to make sure your assumptions are true. You need to identify what formula is causing the negatives. I don't really think it is Ken's code, so there must be something about your data or formulas causing this.

Also, you are using two "whilereadingrecords" formulas, but expecting the second one to be evaluated after the first. Try changing both or at least the second one (times) to whileprintingrecords. I'm wondering if the second formula is picking up the previous value of the first formula, instead of the value for the current record.

-LB