Loop through dates in formula
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=13368
Printed Date: 02 May 2024 at 5:13am
Topic: Loop through dates in formula
Posted By: delstar
Subject: Loop through dates in formula
Date Posted: 31 May 2011 at 5:54am
Recently, I developed a report that displays the time difference in hours between the last update and the current time for a workflow based application we have. The users of this report have expressed a desire that the time takes only working hours into account (8 hrs/day, 5 days/week). I've seen many complicated ways to handle this, and I figured the best solution would be to take the total, divide by three (to account for 8hrs/day), then loop through the dates and subtract 8 if DayOfWeek is Saturday or Sunday. The formula I came up with is as follows:
numbervar ttime:=(datediff('n',{Command.LastUDate},currentdatetime)/60)/3;
datetimevar ddate:= {Command.LastUDate};
datetimevar i;
For i:=ddate to currentdatetime do
(
if dayofweek(i) in ['Saturday','Sunday'] Then ttime:=ttime-8;
);
ttime
Unfortunately, the For loop does not work with dates. Does anyone know of a good way to handle this?
|
Replies:
Posted By: Keikoku
Date Posted: 31 May 2011 at 8:00am
Perhaps you can get the difference in number of days and then do the math on that. Skip to the next post for a summary.
This is the general idea:
We know that there are 7 days in a week.
This means that for every multiple of 7, you will definitely have encountered the weekend.
We then have some remaining days between 0 to 6 inclusively, and depending on which day the update was run, you may run into zero, one, or two off days.
So total working time can be written as
working_time = total_time - (weekends) - (# of off days in remainder)
=============
The total number of weeks elapsed can be determined by simply dividing total days by 7
weeks = Totaldays / 7
daysOff = weeks * 2 (saturday and sunday)
So immediately, you can take off the hours for those days.
Now we have the remaining days of the week left, which is the remainder (mod function...I think it is the same in crystal)
RemainingDays = Totaldays mod 7
Suppose the last update was 30 days ago. That means you definitely came across 4 weekends, and you're left with 2 days remaining.
If you updated on a friday, then you would've run into the weekend for those 2 days.
Alternatively, if it occurred on a sunday, the next 2 days would be working hours.
You know the day that the update was run, and that day can be represented as a number (1 through 7 I believe, for each day of the week).
The only days you want are 1 through 5; anything else should be subtracted.
Suppose we started on 4. Those two days would be 5 and 6. What if we just took the remainder as well?
(start_date + days_remain) mod weekdays
= (4 + 2 ) mod 5
= 1
That leaves us with 1, which we expect since we run into saturday.
Similarly, suppose we started on friday. It would be (5 + 2) mod 5 = 2, which is expected since we run into sat and sun.
And of course, if we start on say monday, we'd have (1 + 2) mod 5 = 0, which means there were no off days in the remaining period.
So your final formula for determining the number of hours to take off would be
work_time = [totalTime - (weeks off) - (days off)]
work_time = [ttime - (8 * (TotalDays / 7 * 2)) - (8 * (start_day + (Totaldays mod 7) mod 5)]
Or something like that.
That would be the idea from a pure mathematical approach, but here's a case that it fails:
Total days = 34, Update day = friday
4 weeks + 3 days remaining
So my formula claims that (5 + 3) mod 5 = 3 is the number of off days you encounter. Clearly that's not true.
Maybe I'm missing something. There might be an easier solution.
EDIT: some brute force checking:
if start day + remaining >= 7, then we have run into two off days (cause it passed both sat and sun)
If start day + remaining = 6, then we have run into one off day (only sat)
if start day + remaining < 6, then we have run into no off days (didn't even reach saturday)
|
Posted By: Keikoku
Date Posted: 31 May 2011 at 8:30am
A summary that seems to work:
Get the difference between update day and today, in days.
step 1: get the number of weeks passed, and subtract (8 * weeks * 2) from total_time
step 2: get the number of days remaining
step 3: add the update day and days remaining
step 4: following cases for step 3:
if result < 6, then no off days
if result = 6, then one off day (subtract 8 * 1)
if result > 6, then two off days (subtract 8 * 2)
Case: updated on thursday, 30 days ago
step 1: 30 / 7 = 4 weeks passed
step 2: 30 mod 7 = 2 days remaining
step 3: start day + days remain = 4 + 2 = 6
step 4: since the result is 6, then we only have one off day for the remaining period.
Case: Updated on monday, 18 days ago.
step 1: 18 / 7 = 2 weeks
step 2: 18 mod 7 = 4 days remaining
step 3: 1 + 4 = 5
step 4: since result is 5, we didn't run into any off days in the remaining period.
|
|