Calculate working hours between dates 1

[Georgedooley]

hi
I'm trying to calculate working hours from the open time of a ticket to the close time.
Working hours are
Mon - Fri 6am - 7pm
Saturday - 6am - 10am.

I can do the mon - fri calculation but because the hours are different on saturday its giving me some problems.

Formula used is.
//Working Days
WhileReadingRecords;
Local DateVar Start := date({@Open Date});
Local DateVar End := date({@Close Date});
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

-------------------------------------------------
//Working Hours

WhileReadingRecords;
NumberVar Days := {@working days};
TimeVar SetStart := TimeValue( "6:00");
TimeVar SetEnd := TimeValue("19:00");
TimeVar StartTime := TimeValue({@Open Date});
TimeVar EndTime := TimeValue({@Close Date});


Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)

Thanks in advance.

 

[Georgedooley]

Can anyone help me with this?

 

[FireGeek21]

How about this??? I found an article Ken Hamady wrote and kept it around just in case. I suspect it may work for you...

WhileReadingRecords;
NumberVar Days := {@Business Days Formula}; // 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({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)


I am also posting the url for the article address.

Hope it helps!

RSGeek

 

[Georgedooley]

Thanks RsGeek

I have already used this formula to calcualte working hours between 6am to 7pm monday to friday but the problem i have is I also need to calculate times between 6am and 10am on saturdays.

Any suggestions?

 

[FireGeek21]

My apologies...

 

[lbass]

You should be able to use the Ken Hamady formula just for days M-F and then add the results of another Hamady formula for number of specific days within the range and then multiply that times the number of hours per Saturday, e.g.,:

datediff("ww",Start-1, End,7)*16 //7 for Saturday, 16 for Sat hours

-LB

 

[FireGeek21]

LBass,

Glad you could help him out. After I got thru my issue I was going to attempt to visit this one.

Ken has some good stuff!

RSGeek

 

[Georgedooley]

Thanks Lbass and RsGeek

I'm not sure I understand your explanation. Do I create a new formula for saturday or add to my working days formula?

Looking forward to your reply.

 

[Georgedooley]

LBass, RsGeek.

Any help on this please?

Thanks

 

[FireGeek21]

George,

I would make 3 formulas.

@Weekdays - to calculate the number of hours Monday-Friday

@Saturdays - to calculate the number of hours on Saturday

@Hours = @Weekdays + @Saturdays

Hope this helps.

RSGeek


RSGeek
(currently using Crystal Reports XI with Lawson 8.03)

 

[lbass]

Sorry, I lost track of this. I think you could just do this:

//Working HoursWhileReadingRecords;
NumberVar Days := {@working days};
TimeVar SetStart := TimeValue( "6:00");
TimeVar SetEnd := TimeValue("19:00");
TimeVar StartTime := TimeValue({@Open Date});
TimeVar EndTime := TimeValue({@Close Date});
Days * ((SetEnd - SetStart) / 3600)
- ((SetEnd - EndTime) / 3600)
- ((StartTime - SetStart) / 3600)
-datediff("ww",Start-1, End,7)*9

So essentially you are calculating the hours for all working days including Saturday, and then subtracting the number of hours less on Saturdays (that you are not open for business)--9 hours.

-LB