DateTime Calculation
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20830
Printed Date: 03 May 2024 at 10:09am
Topic: DateTime Calculation
Posted By: korey_premier
Subject: DateTime Calculation
Date Posted: 24 Jun 2014 at 3:16am
Hello!
I have discovered several great posts in this forum that help with DateTime calculations but I'm having trouble using one of these formulas in a report.
I need to calculate a number of hours between a task creation date and a task completion date. The calculation should exclude non-business hours and weekends. The formula seen below works well until I get to a weekend. Each workday (07:00-17:00) amounts to 10 hours added to the total. Both days of each weekend should be ignored but instead, the formula only ignores one of the days. For entries that cross weekends, my total is exactly 10 hours too high.
I'm sure someone with more experience would be able to spot my error quickly.
Thanks in advance for any help!
Korey
DATETIMEVAR StDate:= {TASK.CREATE_DATE};
DATETIMEVAR EndDate:= {TASK.COMPLETED_DATE};
NUMBERVAR Weeks;
NUMBERVAR Days;
TIMEVAR SLA_Open := TIME(7,0,0);
TIMEVAR SLA_Close := TIME(17,00,0);
NumberVar WeekendTime ;
NUMBERVAR NonWorkTime ;
NUMBERVAR Weeks;
NUMBERVAR Days;
IF WeekDayName(DAYOFWEEK(StDate)) = "Saturday" THEN
StDate:= DATETIMEVALUE(DATE(DATEADD('D',1,StDate)) , SLA_Open);
IF WeekDayName(DAYOFWEEK(StDate)) = "Sunday" THEN
StDate:= DATETIMEVALUE(DATE(DATEADD('D',1,StDate)) , SLA_Open);
IF TIME(StDate) > SLA_Close THEN
StDate := DATETIMEVALUE(DATE(StDate) , SLA_Close);
IF TIME(StDate) < SLA_Open THEN
StDate := DATETIMEVALUE(DATE(StDate) , SLA_Open);
IF WeekDayName(DAYOFWEEK(endDate)) = "Saturday" THEN
endDate = DATETIMEVALUE(DATE(DATEADD('D',1,endDate)) , SLA_Open);
IF WeekDayName(DAYOFWEEK(endDate)) = "Sunday" THEN
endDate = DATETIMEVALUE(DATE(DATEADD('D',1,endDate)) , SLA_Open);
IF TIME(endDate) > SLA_Close THEN
endDate := DATETIMEVALUE(DATE(endDate) , SLA_Close);
IF TIME(endDate) < SLA_Open THEN
endDate := DATETIMEVALUE(DATE(endDate) , SLA_Open);
Weeks:= (Truncate (EndDate - dayofWeek(EndDate) + 1 - (StDate - dayofWeek(StDate) + 1)) /7 ) * 5;
Days := DayOfWeek(EndDate) - DayOfWeek(StDate) + (if DayOfWeek(StDate) = 1 then -1 else 0) +
(if DayOfWeek(EndDate) = 7 then -1 else 0);
// Non Worktime on Business days
NonWorkTime := DATEDIFF("N",DATETIMEVALUE(CurrentDate, SLA_Close),DATETIMEVALUE(CurrentDate+1,SLA_Open)) * (Weeks + Days);
//a weekend in minutes is Count of saturdays and sundays * 24 hours * 60 minutes
WeekendTime := (DateDiff("Ww", stDate ,Enddate , crSaturday) + DateDiff("Ww", stDate, Enddate, crSunday)) * 24 * 60;
(DATEDIFF("N", stDATE, endDate)- NonWorkTime - WeekendTime)/60
|
Replies:
Posted By: Gurbs
Date Posted: 24 Jun 2014 at 3:37am
IF WeekDayName(DAYOFWEEK(StDate)) = "Saturday" THEN
StDate:= DATETIMEVALUE(DATE(DATEADD('D',1,StDate)) , SLA_Open);
You are saying that you want to add 1 day if the start day is Saturday. This makes the start day on Sunday instead of Monday. Change this to
IF WeekDayName(DAYOFWEEK(StDate)) = "Saturday" THEN
StDate:= DATETIMEVALUE(DATE(DATEADD('D',2,StDate)) , SLA_Open);
and you should be good
Edit: Same counts for this part:
IF WeekDayName(DAYOFWEEK(endDate)) = "Saturday" THEN
endDate = DATETIMEVALUE(DATE(DATEADD('D',1,endDate)) , SLA_Open);
|
Posted By: korey_premier
Date Posted: 24 Jun 2014 at 4:22am
Thanks for the suggestion Gurbs.
Were you able to get this to work?
The 'DATEADD' amount was one of the things I noticed that didn't seem to fit but adjusting the amount to 2 doesn't seem to change the result.
Any other thoughts?
|
|