Business Hours (where End < Start)

[hanchilicious]

Hi,

I'm on Crystal 8.5 querying a Remedy database on SQL Server.

I'm working on a formula which will provide business time elapsed between two dates.

Now, where the start time is always less than end time - like your typical work day, the formula I'm using works fine. But, I'm also interested in getting international data. As our global systems share one box, I can only really use on system time, meaning that for the States, for example, whose business day is 6am to 9pm EST - we would simply compensate for the time difference in Crystal, thus making the American business day 11am to 2am.

This creates problems because where I would ordinarily be able to get away with BusinessEndTime-BusinessStartTime or X in BusinessStartTime to BusinessEndTime, I now have a problem, because Crystal can't really understand that I mean 11am to 2am the next morning.

I can't force the formula to say something like 11am to Date+1 2am, because this will mess up intra-day results.

I've tried to use Ken Hamady's business hours formulas (

http://www.kenhamady.com/form13),

but I don't seem to able to get this to return what I expect.

Below is the formula I'm using. As I say, the EMEA hours are fine - as the business day is 0900 to 1700. If you could help me get the correct results for American hours (1100 to 0200), that would be absolutely indescribably great for me.

Below is the formula I'm currently using. If you think it's better to modify this, or can think up an alternative, please let me know.

Thanks so very much.

<H>

//CALCULATE THE NUMBER OF BUSINESS HOURS
//BETWEEN FirstDateTime AND LastDateTime
WhilePrintingRecords;
DatetimeVar FirstDateTime:= {@ExpTimeResolve};
DatetimeVar LastDateTime:= {@Resolve Time};

If {CHG_CHANGE.REGION} = 'EMEA' Then
TimeVar BusinessStartTime:= Time(9,00,00);

If {CHG_CHANGE.REGION} = 'EMEA' Then
TimeVar BusinessEndTime:= Time(17,00,00);

If {CHG_CHANGE.REGION} = 'AMERICAS' Then
TimeVar BusinessStartTime:= Time(11,00,00);

If {CHG_CHANGE.REGION} = 'AMERICAS' Then
TimeVar BusinessEndTime:= Time(02,00,00);

Numbervar HoursInADay:= (BusinessEndTime - BusinessStartTime)/3600;
Numbervar Days2;
Numbervar Weekends;
Numbervar FinalDays;
DateVar StartDate;
DateVar EndDate;
NumberVar HalfDays;
NumberVar FullDays;
NumberVar Hours2;
timevar TrueStartTime;
timevar TrueEndTime;

//FINISH FORMULA IF FirstDateTime OR LastDateTime IS NULL
//*********************************************************
IF FirstDateTime <=Date(0,0,0) or LastDateTime <=Date(0,0,0) then Hours2:= 0

//ELSE ASSIGN Hours2
//*********************************************************
ELSE
(
//ASSIGN FirstDateTime and LastDateTime
//*********************************************************
//Determine whether FirstDateTime falls within
//Start Time to End Time
if time(FirstDateTime) in BusinessStartTime to BusinessEndTime then
FirstDateTime:= FirstDateTime
else if time(FirstDateTime) > BusinessEndTime then
FirstDateTime:= datetime(date(FirstDateTime)+1, BusinessStartTime)
else if time(FirstDateTime) < BusinessStartTime then
FirstDateTime:= datetime(date(FirstDateTime), BusinessStartTime);

//Determine whether LastDateTime falls within Start Time to End Time
//If {CHG_CHANGE.REGION} = 'EMEA'
//Then
if time(LastDateTime) in BusinessStartTime to BusinessEndTime then
LastDateTime:= LastDateTime
else if time(LastDateTime) > BusinessEndTime then
LastDateTime:= datetime(date(LastDateTime), BusinessEndTime)
else if time(LastDateTime) < BusinessStartTime then
LastDateTime:= datetime(date(LastDateTime)-1, BusinessEndTime);
//
// I commented out this bit, because I realised I can't
// treat American business hours with 'in' and 'end - start'
//
//Workout American LastDateTimes
//If {CHG_CHANGE.REGION} = 'AMERICAS'
//Then
//if time(LastDateTime) in Time(0,0,0) to Time(2,0,0) then
// LastDateTime:= datetime(date(LastDateTime)+1, BusinessEndTime)
//else if time(LastDateTime) > BusinessEndTime then
// LastDateTime:= datetime(date(LastDateTime), BusinessEndTime)
//else if time(LastDateTime) < BusinessStartTime then
// LastDateTime:= datetime(date(LastDateTime)-1, BusinessEndTime);

////Determine whether LastDateTime falls within Start Time to End Time
//if time(LastDateTime) in BusinessStartTime to BusinessEndTime then
// LastDateTime:= LastDateTime
//else if time(LastDateTime) > BusinessEndTime then
// LastDateTime:= datetime(date(LastDateTime), BusinessEndTime)
//else if time(LastDateTime) < BusinessStartTime then
// LastDateTime:= datetime(date(LastDateTime)-1, BusinessEndTime);

//ASSIGN STARTDATE and ENDDATE
//*********************************************************
//if the first day falls on a weekend, StartDate is equal to the following Monday for calculation reasons
If DayOfWeek(FirstDateTime) = 7 Then
StartDate := date(FirstDateTime) + 2
Else If DayOfWeek(FirstDateTime) = 1 Then
StartDate := date(FirstDateTime) + 1
Else StartDate:=date(FirstDateTime);

//if the last day falls on a weekend, EndDate is equal to the following Monday for calculation reasons
If DayOfWeek(LastDateTime) = 7 Then
EndDate := date(LastDateTime) + 2
Else If DayOfWeek(LastDateTime) = 1 Then
EndDate := date(LastDateTime) + 1
Else EndDate := date(LastDateTime);

//CALCULATE Days2 AND WEEKENDS
//*********************************************************
//Calculate Days2 (including First day and Last day)
Days2:= (EndDate - StartDate)+1;

//Calculate weekends
if Days2 >= 7 then
WeekEnds := (Round((Days2/7),0))*2
else if DayOfWeek(StartDate) > DayOfWeek(EndDate) then
WeekEnds := 2
else WeekEnds := 0;

//CALCULATE FinalDays
//*********************************************************
//If the Last Day is on a weekend then FinalDays subtract the weekend days
If DayOfWeek(LastDateTime) = 7 then FinalDays:= FinalDays - 1;
If DayOfWeek(LastDateTime) = 1 then FinalDays:= FinalDays - 2;

//Assign FinalDays to Days2 minus Weekends
FinalDays:= Days2 - WeekEnds;

//CALCULATE HOURS
//*********************************************************
//Calculate FirstDateTime and LastDateTime if falling on a weekend
//if the first day falls on a weekend, StartDate is equal to the following Monday for calculation reasons
If DayOfWeek(FirstDateTime) = 7 Then
FirstDateTime := datetime(date(FirstDateTime) + 2, BusinessStartTime)
Else If DayOfWeek(FirstDateTime) = 1 Then
FirstDateTime := datetime(date(FirstDateTime) + 1, BusinessStartTime);

//if the last day falls on a weekend, EndDate is equal to
//the following Monday for calculation reasons
If DayOfWeek(LastDateTime) = 7 Then
LastDateTime := datetime(date(LastDateTime) + 2,BusinessStartTime)
Else If DayOfWeek(LastDateTime) = 1 Then
LastDateTime := datetime(date(LastDateTime) + 1, BusinessStartTime);

//If no more than 24 hours involved
If FinalDays <= 1 then
(
//If first day is the same day as last day
if date(FirstDateTime) = date(LastDateTime) then
(
//If First Day starts before business start time, assign TrueStartTime to business starttime
if time(FirstDateTime) >= BusinessStartTime then
TrueStartTime:= time(FirstDateTime)
else TrueStartTime:= BusinessStartTime;

//If Last Day ends after business end time, assign TrueEndTime to business endtime
if time(LastDateTime) <= BusinessEndTime then
TrueEndTime:= time(LastDateTime)
else TrueEndTime:= BusinessEndTime
)
//If first day is not the same day as last day
else
TrueStarttime:= BusinessStartTime;

if time(LastDateTime) <= BusinessEndTime then
TrueEndTime:= time(LastDateTime)
else TrueEndTime:= BusinessEndTime;

//Assign Hours2 to the endtime - starttime
//divided by 3600 (seconds in an hour)
Hours2:= (TrueEndTime-TrueStartTime)/3600;
)

//Else Hours2 = how many hours on the two half days + how many hours for the full days
Else
(
HalfDays:= ((BusinessEndTime - time(FirstDateTime)) /3600 + (time(LastDateTime) - BusinessStartTime)
/3600);
FullDays:= (FinalDays-2) * HoursInADay;
Hours2:= HalfDays + FullDays;
);
);

//DISPLAY NUMBER OF BUSINESS HOURS IN THE RANGE
//*********************************************************;

StringVar Hr := ToText(Truncate(Hours2),0,""
StringVar Mt := //ToNumber(Right(ToText({@Resolve Time}),0,"&quot,2)-ToNumber(Right(ToText({@ExpTimeResolve}),0,"&quot,2);
ToText(Remainder(Hours2*60,60),0,""
StringVar Hr := Replace(Hr,".",""
Hr + ":" + Mt;

Sorry about the length of the post!!

 

[hanchilicious]

Come on! You can't mean to say that none of you Crystal Jedis don't know how to convert business hours! Don't be put off by my overcomplicated formula ! If you can solve this, there's definitely a star in it for you - or a beer if you're local!!