Distributing number of days over intervals

[ProfessorSnail]

I would like to determine how the period between the start date and the end date are distributed across the intervening months. For example, if a person is absent from the 13th March and returns on the 3rd May, how many days are they absent in each month? I would also like to sum these figures to give a total number of lost days per month.

For example,

Data Set
Name Date absent from Date absent to
John 03 May 2010 04 May 2010
Dave 20 Apr 2010 05 May 2010
Jane 20 May 2010 18 Jul 2010

Report requirement
Mar Apr May Jun Jul
# of lost days 0 10 16 30 18

Thanks in advance for your help.

Sam

 

[lbass]

Create formulas like these:

//{@Mar}:
local datevar start;
local datevar end;
if {table.dtabsfr} < date({?year},3,1) then
start := date({?year},3,1) else
if {table.dtabsfr} in date({?year},3,1) to
date({?year},3,31) then
start := {table.dtabsfr} else
start := date(0,0,0);
if start <> date(0,0,0) then(
if {table.dtabsto} > date({?year},3,31) then
end := date({?year},3,31) else
if {table.dtabsto} in date({?year},3,1) to
date({?year},3,31) then
end := {table.dtabsto}+1 else //to count both start and end days
end := date(0,0,0)
) else
end := date(0,0,0);
datediff("d", start, end);

Repeat for each month, and then place in the detail section. You can insert sums on the formula at a name group and/or a grand total level.

-LB

 

[ProfessorSnail]

Thanks LB. Works a treat!

The only tweak I cannot work out is how to display a zero if no days were lost in the month? I guess there needs to be another 'if' nested somewhere but I'm struggling?

Thanks again for your help.

Sam

 

[ProfessorSnail]

Sorry ignore me. The formula displays the zero fine.