I do a similar thing in many reports where I need to know Days Between.
I have a formula where you insert your start and end dates as below.
This already deals with weekends, but needs another formula called Holidays for public holidays which are non-working days in your country. For the format of that see further down
//Days Between
WhileReadingRecords;
Local DateVar Start := date({Command.Review Date}); // place your Starting Date here
Local DateVar End := date(
{@end_date}); // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateTimeVar 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-1
//Holidays
BeforeReadingRecords;
DateTimeVar Array Holidays := [
datetime (2012,01,02,00,00,00),
datetime (2012,04,06,00,00,00),
datetime (2012,04,09,00,00,00),
datetime (2012,05,07,00,00,00),
datetime (2012,06,04,00,00,00),
datetime (2012,06,05,00,00,00),
datetime (2012,08,27,00,00,00),
datetime (2012,12,25,00,00,00),
datetime (2012,12,26,00,00,00),
datetime (2013,01,01,00,00,00),
datetime (2013,03,29,00,00,00),
..........
datetime (2020,12,28,00,00,00)
];
0
Edited by yggdrasil - 20 Jun 2012 at 1:14am