Date Range manipulation needed

[RachaelD]

I used Ken Hamady's formulas for Business Days and Business Hours and that works great, but the date/time fields I'm using sometimes report back hours and days that I don't want to include in the scope of my work day, therefore my calculations on time to resolve have strange results and show a negative if it's outside the scope.

I'd like to say that if {table.createdate} or {table.closeddate} is not between the hours of 8am and 5pm, then they need to report as 8am the next business day. So 5:01pm to midnight would add a day, and midnight to 7:59am would just become 8am.

I'm using Crystal 10.

Thanks!

 

[CoSpringsGuy]

please include the formula from Ken and lets see if anyone might be able to offer a modification that will return the results you are looking for.

 

[RachaelD]

@Business Days formula:
//Main formula
WhileReadingRecords;
Local DateVar Start := date({HPD_HelpDesk.Create_Time}); // place your Starting Date here
Local DateVar End := date({@Resolved or Closed}); // 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 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


@Business Hours formula:
WhileReadingRecords;

NumberVar Days := {@Business Days}; // The field that calculates your business days
TimeVar SetStart := TimeValue( "8:00"); // The start your work day
TimeVar SetEnd := TimeValue("17:00"); // The end your work day
TimeVar StartTime := TimeValue({HPD_HelpDesk.Create_Time});// The data field that holds your Start Time
TimeVar EndTime := TimeValue({@Resolved or Closed}); // 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)

The @Business Hours formula has a negative value if the time is outside the 8-5 M-F period.

Thanks!