I want an excel accounts receivable file to moove receivables to the
appropiate age column as they get olde. For example, if item is 30 days old,
it should automatically move into the 30 day column.

Why a Marco? Why not just set up your sheet:

Customer Date Amount Current 30 60 90

Then use if formulas in the 30 60 and 90 columns to get the value out of the
amount column if appropriate. It is a whole pile easier than a macro...

For the 30 day column...
=if(and (b2=date() + 30, b2<date()+60)), C2, "")
or something like that (untested formula).
HTH

"Dennis Merchand" wrote:

I want an excel accounts receivable file to moove receivables to the
appropiate age column as they get olde. For example, if item is 30 days old,
it should automatically move into the 30 day column.

Jim's right; you don't need a macro.

Having done something very similar to what Jim showed, I can say it will
help simplify your formulas if you have a "Days Past Due" column which
calculates the days of aging (Aging date minus invoice date). Then you can
just refer to that value in your aging columns.

Also, if you wanted variable aging buckets (ex. 30, 60, 90 -or- 15,30,45),
you could set your headers like Jim showed in his example and then refer to
their values for cutoff purposes.

Example:
Columns a
A | B | C | D | E | F | G | H | I
Customer | Date | Amount | Days Past Due | 0 | 30 | 60 | 90 | 9999
("0" is Current, "9999" is over 90)

Formula in E2 (Current) is
=IF($D2<=$E$1,$C2,0)
Paste this formula down the column.

Formula in F2 (0-30) is
=if(($E1<$D2)*($D2<=F$1),$C2,0) -or- =if(and($E1<$D2),($D2<=F$1)),$C2,0)
=IF((E$1<$D2)*($D2<=F$1),$C2,0) -or- =if(and(E$1<$D2),($D2<=F$1)),$C2,0)
Copy and paste the formula in F2 across the aging columns and down all
applicable rows.

If you don't like seeing 0,30,60,90,9999 and so on, hide that row and insert
a new visible row above it...or use a text box over the "Current" and
"Over..." columns.

-Glenn Ray


"Jim Thomlinson" wrote:

Why a Marco? Why not just set up your sheet:

Customer Date Amount Current 30 60 90

Then use if formulas in the 30 60 and 90 columns to get the value out of the
amount column if appropriate. It is a whole pile easier than a macro...

For the 30 day column...
=if(and (b2=date() + 30, b2<date()+60)), C2, "")
or something like that (untested formula).
HTH

"Dennis Merchand" wrote:

I want an excel accounts receivable file to moove receivables to the
appropiate age column as they get olde. For example, if item is 30 days old,
it should automatically move into the 30 day column.

Having done something very similar to what Jim showed, I can say it will help
simplify your formulas if you have a "Days Past Due" column which calculates
the days of aging (Aging date minus invoice date). Then you can just refer
to that value in your aging columns.

Also, if you wanted variable aging buckets (ex. 30, 60, 90 -or- 15,30,45),
you could set your headers like Jim showed in his example and then refer to
their values for cutoff purposes.

Example:
Columns a
A | B | C | D | E | F | G | H | I
Customer | Date | Amount | Days Past Due | 0 | 30 | 60 | 90 | 9999
("0" is Current, "9999" is over 90)

Formula in E2 (Current) is
=IF($D2<=$E$1,$C2,0)
Paste this formula down the column.

Formula in F2 (0-30) is
=if(($E1<$D2)*($D2<=F$1),$C2,0) -or- =if(and($E1<$D2),($D2<=F$1)),$C2,0)
=IF((E$1<$D2)*($D2<=F$1),$C2,0) -or- =if(and(E$1<$D2),($D2<=F$1)),$C2,0)
Copy and paste the formula in F2 across the aging columns and down all
applicable rows.

If you don't like seeing 0,30,60,90,9999 and so on, hide that row and insert
a new visible row above it...or use a text box over the "Current" and
"Over..." columns.

-Glenn Ray


"Jim Thomlinson" wrote:

Why a Marco? Why not just set up your sheet:

Customer Date Amount Current 30 60 90

Then use if formulas in the 30 60 and 90 columns to get the value out of the
amount column if appropriate. It is a whole pile easier than a macro...

For the 30 day column...
=if(and (b2=date() + 30, b2<date()+60)), C2, "")
or something like that (untested formula).
HTH

"Dennis Merchand" wrote:

I want an excel accounts receivable file to moove receivables to the
appropiate age column as they get olde. For example, if item is 30 days old,
it should automatically move into the 30 day column.

Having done something very similar to what Jim showed, I can say it will help
simplify your formulas if you have a "Days Past Due" column which calculates
the days of aging (Aging date minus invoice date). Then you can just refer
to that value in your aging columns.

Also, if you wanted variable aging buckets (ex. 30, 60, 90 -or- 15,30,45),
you could set your headers like Jim showed in his example and then refer to
their values for cutoff purposes.

Example:
Columns a
A | B | C | D | E | F | G | H | I
Customer | Date | Amount | Days Past Due | 0 | 30 | 60 | 90 | 9999
("0" is Current, "9999" is over 90)

Formula in E2 (Current) is
=IF($D2<=$E$1,$C2,0)
Paste this formula down the column.

Formula in F2 (0-30) is
=if(($E1<$D2)*($D2<=F$1),$C2,0) -or- =if(and($E1<$D2),($D2<=F$1)),$C2,0)
=IF((E$1<$D2)*($D2<=F$1),$C2,0) -or- =if(and(E$1<$D2),($D2<=F$1)),$C2,0)
Copy and paste the formula in F2 across the aging columns and down all
applicable rows.

If you don't like seeing 0,30,60,90,9999 and so on, hide that row and insert
a new visible row above it...or use a text box over the "Current" and
"Over..." columns.

-Glenn Ray


"Jim Thomlinson" wrote:

Why a Marco? Why not just set up your sheet:

Customer Date Amount Current 30 60 90

Then use if formulas in the 30 60 and 90 columns to get the value out of the
amount column if appropriate. It is a whole pile easier than a macro...

For the 30 day column...
=if(and (b2=date() + 30, b2<date()+60)), C2, "")
or something like that (untested formula).
HTH

"Dennis Merchand" wrote:

I want an excel accounts receivable file to moove receivables to the
appropiate age column as they get olde. For example, if item is 30 days old,
it should automatically move into the 30 day column.