Help with defined aging bucket formula

[AcctSolver]

I am trying to write a projected receipts aging report by week. All weeks are specifically numbered, begin on Sunday, and end on Saturday. My buckets need to be 1 ($ in openAmount field due prior to this week, plus anything due this fiscal week), 2 (due next fiscal week), 3 (due next fiscal week plus one) ... until I have reached 7 weeks. The final column should be equal to anything >7 weeks out.

This is not just a 7 day aging report, but specific buckets from Sunday-Saturday. How do I accomplish this? In Excel I manually put in the first day of the fiscal week in a cell, and built all the formulas off of that, =IF(AND(H15>($I$4+6), H15<($I$4+14)),E15,0) with $I$4 equalling the first day of the fiscal week, H15 being the expected payment date and E15 being the open amount.

 

[lbass]

if {table.duedate} <= currentdate-dayofweek(currentdate) + 8 then 1 else
if {table.duedate} <= currentdate-dayofweek(currentdate) + 15 then 2 else
if {table.duedate} <= currentdate-dayofweek(currentdate) + 22 then 3 else //etc. ...
8

You could either group on this or insert this as a column in a crosstab. Then insert a summary on the amount field.

-LB