Business Days - How can i incorporate Minutes? 2

[robcunliffe]

Hi all,

I am using a formulae (using crystal 10) to so that the number of days between two dates ignores weekends. The formula is lifted straight off the business objects web site.

However i have a problem.

I need not only to work out the number of days between the dates (ignoring weekends) but to display as minutes instead of days.

Why do i need to do this?

I am working out the difference between a 'DUE' time and a 'ACTUAL' time. IF the actual time is one minute past the due time then the days late figure needs to be 1.

Here is the formula i have been using to to do this that does not account for weekends.

{@datediff} formula:

datediff ("n", {@dateandtimearrived},{@datetimestarted})/1440

i then apply this formula to 'always round up'

{@roundup} formula
If {@datediff} - truncate({@datediff}) <> 0 then truncate({@datediff})+1 else
{@datediff}

here is the formula i am using to not include weekends.

DateVar FirstDay:= Date({@dateandtimearrived});
DateVar LastDay:= Date({@datetimestarted});
// FirstDay and LastDay should be substituted throughout the
// formula with your date fields.
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;
If DayOfWeek(FirstDay) = 7 Then
StartDate := FirstDay - 1
Else
If DayOfWeek(FirstDay) = 1 Then
StartDate := FirstDay - 2
Else
StartDate:=FirstDay;
If DayOfWeek(LastDay) = 7 Then
EndDate := LastDay + 2
Else If DayOfWeek(LastDay) = 1 Then
EndDate := LastDay + 1
Else
EndDate := LastDay;
Days:= EndDate - StartDate;
WeekEnds := (Truncate(Days/7)*2);
If DayOfWeek(StartDate) > DayOfWeek(EndDate) Then
FinalDays:=Days-Weekends-1
Else FinalDays:=Days-Weekends;
If DayOfWeek(LastDay) = 7 Or DayOfWeek(LastDay) = 1 Then
FinalDays := FinalDays - 1
Else
FinalDays := FinalDays;
FinalDays;

I need a formula that allows all three to work together.

Any help or guidence would be appreciated.

Cheers

Rob Cunliffe

 

[lbass]

Try the following, leaving the datetimes as datetimes:

DateTimeVar FirstDay:= {@datetime1};
DateTimeVar LastDay:= {@datetime2};
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateTImeVar StartDate;
DateTimeVar EndDate;
If DayOfWeek(FirstDay) = 7 Then
StartDate := FirstDay - 1
Else
If DayOfWeek(FirstDay) = 1 Then
StartDate := FirstDay - 2
Else
StartDate:=FirstDay;
If DayOfWeek(LastDay) = 7 Then
EndDate := LastDay + 2
Else If DayOfWeek(LastDay) = 1 Then
EndDate := LastDay + 1
Else
EndDate := LastDay;
Days:= EndDate - StartDate;
WeekEnds := (Truncate(Days/7)*2);
If DayOfWeek(StartDate) > DayOfWeek(EndDate) Then
FinalDays:=Days-Weekends-1
Else FinalDays:=Days-Weekends;
If DayOfWeek(LastDay) = 7 Or DayOfWeek(LastDay) = 1 Then
FinalDays := FinalDays - 1
Else
FinalDays := FinalDays;
-int(-(FinalDays * 1440));

This will give you the rounded number of minutes. Not sure where the "due time" fits in for comparison.

-LB

 

[robcunliffe]

Hi

Many thanks for your help.

The problem is the change you have made only dispalys the number of days as minutes. What i really need is days displayed as a fraction. so one and a half days displayed as: 1.5

ROb

 

[lbass]

I need not only to work out the number of days between the dates (ignoring weekends) but to display as minutes instead of days.

Still unclear on what you're looking for. You could take my formula and change the last line to:

-int(-FinalDays);

This would round up the days to the next day if there were a fraction. If you want the fraction to show, then leave off this last line.

-LB

 

[robcunliffe]

lbass,

I apologise for not being clear.

In my previous working (not excluding weekends) i used the datediff formula.

datediff ("n", {@dateandtimearrived},{@datetimestarted})/1440

I asked the syntax to display the result as minutes, see "n". I then divided by 1440 to put the result in to days so that it would display the number of days as a decimal without rounding up or down.

E.g one and a half days would be displayed as 1.5

a few minutes would be displayed as 0.01 for example.

Hope this is clearer.

 

[lbass]

Please see the last part of my my last post. I still don't know whether that meets your needs, and if not, why not. Please explain.

-LB

 

[robcunliffe]

It works sometimes but not in all cases.

Sometimes it deducts the right number of days (for the weekend and others it does not.

i think the problem lies with this bit of the formula:

WeekEnds := (Truncate(Days/7)*2);

what if the start date was a thursday and the end date the following tuesday:

days= start date - end date = 6

weekends: 6/7 *2 = 1.7

truncate this and get 1

but shouldn't this need to be 2 cause there are 2 days that it doesnt need to count during the period?

Or i am way off the mark?
many thanks for you help.
Rob

 

[lbass]

Here's a variation:

DateTimeVar FirstDay:= {@datetime1};
DateTimeVar LastDay:= {@datetime2};
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateTImeVar StartDate;
DateTimeVar EndDate;
If DayOfWeek(FirstDay) = 7 Then
StartDate := datetime(date(FirstDay + 2),time(9,0,0)) //enter starttime of the business day
Else
If DayOfWeek(FirstDay) = 1 Then
StartDate := datetime(date(FirstDay + 1),time(9,0,0))
Else
StartDate:=FirstDay;
If DayOfWeek(LastDay) = 7 Then
EndDate := datetime(date(LastDay +2),time(9,0,0))
Else If DayOfWeek(LastDay) = 1 Then
EndDate := datetime(date(LastDay +1),time(9,0,0))
Else
EndDate := LastDay;
Days:= EndDate - StartDate + 1;
WeekEnds := (EndDate - dayofweek(EndDate-1)) - (StartDate+ 7-dayofweek(StartDate)) + 1;
if WeekEnds = 2 then
WeekEnds := WeekEnds else
if WeekEnds <= 0 then
WeekEnds := 0 else
WeekEnds := round(((WeekEnds -2)/7) * 2 + 2);
FinalDays:=Days-Weekends-1;
if FinalDays < 0 then
FinalDays := 0;
FinalDays;
-int(-FinalDays);

This changes startdates or enddates that fall on Saturdays or Sundays to Monday at 9:00AM. Change the start time to reflect your business starttime.

This seems to be work when I test it. This is an adaptation from the Business Objects technical paper called "Calculating Business Days or Hours within a Date Range".

-LB

 

[robcunliffe]

thank you immensley, this is perfect. Thanks very much