robcunliffe
Technical User
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
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