OK, I may have made this more complicated than it needs to be but without a full understanding of what is and isn't possible in terms of the data I have endeavoured to cover every possibility (every one I can think of anyway). The one assumption that I have made is that only days up to the date the report is run are counted. In other words, if the record has an end date of 25 September, when run on 12 September will only include 12 days for the relevant month.
The three formulas now look like this:
[Code {@Mth1}]
WhilePrintingRecords;
Local DateVar END;
If Isnull({Table.EndDate})
Then If Month({Table.StartDate}) = Month(CurrentDate)
Then If {Table.StartDate} > CurrentDate
Then END := Date({Table.StartDate})
Else END := CurrentDate
Else END := DateSerial(Year({Table.StartDate}), Month({Table.StartDate})+1,1) -1
Else
If Month({Table.StartDate}) = Month(CurrentDate)
Then If {Table.EndDate} > CurrentDate
Then END := CurrentDate
Else END := Date({Table.EndDate})
Else
If Month({Table.EndDate}) = Month({Table.StartDate})
Then If {Table.EndDate} > CurrentDate
Then END := CurrentDate
Else END := Date({Table.EndDate})
Else END := DateSerial(Year({Table.StartDate}), Month({Table.StartDate})+1,1) -1;
END - {Table.StartDate}
[/Code]
[Code {@Mth2}]
If Isnull({Table.EndDate})
Then If Month({Table.StartDate}) = Month(CurrentDate)
Then 0
Else
If Month({Table.StartDate}) = Month(CurrentDate) - 1
Then CurrentDate - Minimum (MonthToDate) + 1
Else DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 1, 1)
Else
If Month({Table.StartDate}) = Month({Table.EndDate}) or
Month({Table.StartDate}) = Month(CurrentDate)
Then 0
Else
If Month({Table.EndDate}) - Month({Table.StartDate}) = 1
Then Day({Table.EndDate})
Else
If {Table.EndDate} <= CurrentDate
Then DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 1, 1)
Else
If Month({Table.StartDate}) = Month(CurrentDate) - 1
Then CurrentDate - Minimum(MonthToDate)
Else DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 1, 1)
[/Code]
[Code {@Mth3}]
If Isnull({Table.EndDate})
Then If Month({Table.StartDate}) in [Month(CurrentDate) to Month(CurrentDate) -1]
Then 0
Else
If Month({Table.StartDate}) = Month(CurrentDate) - 2
Then CurrentDate - Minimum (MonthToDate) + 1
Else 0
Else
If Month({Table.EndDate}) - Month({Table.StartDate}) < 2
Then 0
Else If {Table.EndDate} > CurrentDate
Then CurrentDate - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) + 1
Else {Table.EndDate} - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) + 1
[/Code]
I have tested the code pretty extensively so I believe it works for the sample data I created. If I have misinterpreted the requirements then it obviously won't return the correct results. With the benefit of a thorough understanding of data (date) combinations it may well be possible to simplify the code.
Hope this helps.
Cheers
Pete