calculating working days in a report thats grouped by month

[nathanharcup]

Hi All,
I'm having a few problems returning the correct data in my report. I have used the working hours formula that I found at

http://www.kenhamady.com/formula.html

My problem is that I need the report to show the whole month and not just the days in which records where created so I created a new DB table to hold dates from 1/1/2010 to 1/1/2020
I then have a table that with the records and the date they were added

I have grouped my report by the DB table that holds all dates and put in the working days formula. Now when I run the report it's fine

If I now try to could records it shows me incorect working days.

My table joins are

DB Dates to recordtable.Date (left outter join)

thanks

 

[nathanharcup]

I can now see where the issue is:-


DateTable recordtable workingdaysformula
31/1/2010 31/1/2010 1
31/1/2010 31/1/2010 1

Is there a way to change the working days formula to work inside a group and only count unique working days?

Thanks

 

[IanWaterman]

You need to show the contents of workingdaysformula

Ian

 

[nathanharcup]

Content as per the below

//Main formula
WhileReadingRecords;

Local DateVar Start := date({DATES.DAY}); // place your Starting Date here
Local DateVar End := date({DATES.DAY}); // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;


Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol