Thanks:  0
Likes:  0

# Thread: Aging Invoice Calculator - Excel Formula SNAFU

1. ## Aging Invoice Calculator - Excel Formula SNAFU

I have an excel sheet that I want to calculate total of invoices that are 31-60, 61-90 and over 90 days old. I can only get the formula to calculate based on what MONTH it currently is. I have a cell that contains a DATE, what I would like to do is be able to put any date in that cell and the formula calculate the results accordingly based on that date. I will follow this post with an attempt to display the sheet here in teh forum...

Regards

2. ## Re: Aging Invoice Calculator - Excel Formula SNAFU

Using a PivotTable...

******** ******************** ************************************************************************>
 Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D2D3D4 =

A
B
C
D
E
F
G
H
I
J
K
L
M
N
1
InvoiceDateAmountAge Sum of Amount
2
1004A7/15/200310140 AgeTotal
3
1005B9/25/20031568 31-6025
4
2012B10/13/20032550 61-9015
5
>9010
6
Grand Total50
7
8
 Sheet3

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

3. ## Re: Aging Invoice Calculator - Excel Formula SNAFU

That's ALMOST IT!
I see how it uses the TODAY in calculating, but I guess what I want to do is take the amount in a cell IF it's 31-60 days old and add it with others that meet the same criteria.

I must be able to change the date that the formul is based on, such as not TODAY but enter any other date and calculate based on that date. That way, I can forcast what the aging report is going to look like on future dates.

Am I confused?

4. ## Re: Aging Invoice Calculator - Excel Formula SNAFU

Originally Posted by bristolxyz
That's ALMOST IT!
I see how it uses the TODAY in calculating, but I guess what I want to do is take the amount in a cell IF it's 31-60 days old and add it with others that meet the same criteria.

I must be able to change the date that the formul is based on, such as not TODAY but enter any other date and calculate based on that date. That way, I can forcast what the aging report is going to look like on future dates.

Am I confused?
Simply replace the TODAY() worksheet function with a reference to a cell containing a date that you want to use in the comparison.

5. ## Re: Aging Invoice Calculator - Excel Formula SNAFU

******** ******************** ************************************************************************>
 Microsoft Excel - Aging Debtors.xls ___Running: xl2002 XP : OS = Windows XP
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 D1D4E4F4G4D5E5F5G5D6E6F6G6D7E7F7G7D8E8F8G8D9E9F9G9D10E10F10G10D11E11F11G11D12E12F12G12D13E13F13G13D14E14F14G14D15E15F15G15 =

A
B
C
D
E
F
G
H
1
Date:3-Dec-03
2
3
Invoice #DateAmountCurrentOver 30Over 60Over 90
4
18-Jan-03\$100.00

\$100.00
5
231-Mar-03\$150.00

\$150.00
6
330-Jun-03\$75.00

\$75.00
7
415-Aug-03\$30.00

\$30.00
8
522-Aug-03\$50.00

\$50.00
9
65-Sep-03\$28.00

\$28.00
10
729-Sep-03\$15.00

\$15.00
11
811-Oct-03\$26.00
\$26.00

12
922-Oct-03\$150.00
\$150.00

13
104-Nov-03\$66.00\$66.00

14
1125-Nov-03\$82.00\$82.00

15
121-Dec-03\$55.00\$55.00

16
 Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Change the date in cell D1.

D4: =IF(\$D\$1-B4<=30,C4,"")
E4: =IF(AND(\$D\$1-B4>=31,\$D\$1-B4<=60),C4,"")
F4: =IF(AND(\$D\$1-B4>=61,\$D\$1-B4<=90),C4,"")
G4: =IF(\$D\$1-B4>90,C4,"")

Copy down.

You may have to tweak the formulas to get exactly what you want.

HTH

Mike

6. ## Re: Aging Invoice Calculator - Excel Formula SNAFU

Ekim this is just what I need!! The only problem is I don't know how to get it to adjust the aging if a payment has been applied against the invoice. Hope 10 years later you're still around to help, or someone can

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•