Need to find a way to add difference of days

[satinsilhouette]

What I need:

I need to be able to count this formula:

if {@Bus Days Fwd to AU} < 2 then 1

So if it is < 2 then 1, should register as one record to be summarzied for the overall report and for a group.

BUT - I don't see it to summarize. Nor do I see it to build a running total, or Manual RT.

This forumula, {@Bus Days Fwd to AU}, is stemmed of another formula:


{@Bus Hrs Fwd to AU}/9

This formula takes the Business Hours in a work day and divides it by 9 to give the number of days back, per Ken Hamandy (sry if sp is incorrect).

So here is the formula that precedes that one:

//whilereadingrecords;

DatetimeVar FirstDateTime:= Minimum ({AP_INVOICE_RPT.INV_SCAN_DT}, {AP_INVOICE_RPT.INVOICE_ID});
//datetime(2007, 11, 06, 16,39,00);
//{@STARTDATE};

DatetimeVar LastDateTime:= Minimum ({AP_INVOICE_RPT.FWD_AU_DT}, {AP_INVOICE_RPT.INVOICE_ID});
//{@FINISHDATE};
//datetime(2007, 11, 09, 08,27,00);

TimeVar BusinessStartTime:= Time(08,00,00);

TimeVar BusinessEndTime:= Time(17,00,00);

Numbervar HoursInADay:= (BusinessEndTime - BusinessStartTime)/3600;
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;
NumberVar halfdays;
NumberVar fulldays;
NumberVar hours;
//NumberVar Holidays:=0;
timevar TrueStartTime;
timevar TrueEndTime;

IF FirstDateTime <=Date(0,0,0) or LastDateTime <=Date(0,0,0) then hours:= 0
ELSE
(if time(FirstDateTime) in BusinessStartTime to BusinessEndTime then
FirstDateTime:= FirstDateTime
else if time(FirstDateTime) > BusinessEndTime then
FirstDateTime:= datetime(date(FirstDateTime)+1, BusinessStartTime)
else if time(FirstDateTime) < BusinessStartTime then
FirstDateTime:= datetime(date(FirstDateTime), BusinessStartTime);

if time(LastDateTime) in BusinessStartTime to BusinessEndTime then
LastDateTime:= LastDateTime
else if time(LastDateTime) > BusinessEndTime then
LastDateTime:= datetime(date(LastDateTime), BusinessEndTime)
else if time(LastDateTime) < BusinessStartTime then
LastDateTime:= datetime(date(LastDateTime)-1, BusinessEndTime);

If DayOfWeek(FirstDateTime) = 7 Then
StartDate := date(FirstDateTime) + 2
Else If DayOfWeek(FirstDateTime) = 1 Then
StartDate := date(FirstDateTime) + 1
Else StartDate:=date(FirstDateTime);
If DayOfWeek(LastDateTime) = 7 Then
EndDate := date(LastDateTime) + 2
Else If DayOfWeek(LastDateTime) = 1 Then
EndDate := date(LastDateTime) + 1
Else EndDate := date(LastDateTime);
Days:= (EndDate - StartDate)+1;
if Days >= 7 then
WeekEnds := (Round((Days/7),0))*2
else if DayOfWeek(StartDate) > DayOfWeek(EndDate) then
WeekEnds := 2
else WeekEnds := 0;
If DayOfWeek(LastDateTime) = 7 then
FinalDays:= FinalDays - 1;
If DayOfWeek(LastDateTime) = 1 then
FinalDays:= FinalDays - 2;
FinalDays:= Days - WeekEnds;
//If day of week falls on Sunday or Saturday
If DayOfWeek(FirstDateTime) = 7 Then
FirstDateTime := datetime(date(FirstDateTime) + 2, BusinessStartTime)
Else If DayOfWeek(FirstDateTime) = 1 Then
FirstDateTime := datetime(date(FirstDateTime) + 1, BusinessStartTime);

If DayOfWeek(LastDateTime) = 7 Then
LastDateTime := datetime(date(LastDateTime) + 2,BusinessStartTime)
Else If DayOfWeek(LastDateTime) = 1 Then
LastDateTime := datetime(date(LastDateTime) + 1, BusinessStartTime);

//If less than 24 hours involved
If FinalDays <= 1 then
(
//If first day is the same day as last day
if date(FirstDateTime) = date(LastDateTime) then
(
//If First Day starts before business start time, //assign TrueStartTime to business starttime
if time(FirstDateTime) >= BusinessStartTime then
TrueStartTime:= time(FirstDateTime)
else TrueStartTime:= BusinessStartTime;

//If Last Day ends after business end time, assign //TrueEndTime to business endtime
if time(LastDateTime) <= BusinessEndTime then
TrueEndTime:= time(LastDateTime)
else TrueEndTime:= BusinessEndTime
)
//If first day is not the same day as last day
else TrueStarttime:= BusinessStartTime;

if time(LastDateTime) <= BusinessEndTime then TrueEndTime:= time(LastDateTime)
else TrueEndTime:= BusinessEndTime;

//Assign hours to the endtime - starttime divided by 3600 (seconds in an hour)
hours:= (TrueEndTime-TrueStartTime)/3600;
)
Else
(
halfdays:= ((BusinessEndTime - time(FirstDateTime)) /3600 + (time(LastDateTime) - BusinessStartTime)
/3600);
fulldays:= (FinalDays-2) * HoursInADay;
hours:= halfdays + fulldays;
);
);

I can't summarize this and have not found another easy way to get to the answer in this report. If someone has a suggestion as to how to get to a workday with 9 hours and then for me to find out if it is less than 2 work days, I would greatly appreciate it.

Thanks!



Thanks so much!
satinsilhouette

 

[Madawc]

I'm not sure I follow what you're after. DateDiff can find differences in whole days. If time is a factor you could adjust for it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You would have to use another variable, as in:

Add this to the section where {@Bus Days Fwd to AU} is placed:
whileprintingrecords;
numbervar grpsum;
numbervar gt;
if {@Bus Days Fwd to AU} < 2 then (
grpsum := grpsum + 1;
gt := gt + 1
);

Then add a reset formula in the group header:
whileprintingrecords;
numbervar grpsum;
if not inrepeatedgroupheader then
grpsum := 0;

Then in the group footer, use the following to display the result:
whileprintingrecords;
numbervar grpsum;

Display the grand total in the report footer:
whileprintingrecords;
numbervar gt;

-LB