| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
creating an aging report
Hi,
I'm trying to create an Accounts Receivable "aging report", using Excel 2003. I've got a field (a date field), that provides the date of invoice. I'd like then to add 30, 60, or 90 days from that date, and call that the "Due Date" for payment in another column. Thanks, Peter |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an aging report
Excel dates are just numbers so if your invoice is in a date format that
excel recognizes just add =A1+30 =A1+60 and do on where A1 holds the date -- Regards, Peo Sjoblom (No private emails please) "PSikes" wrote in message ... Hi, I'm trying to create an Accounts Receivable "aging report", using Excel 2003. I've got a field (a date field), that provides the date of invoice. I'd like then to add 30, 60, or 90 days from that date, and call that the "Due Date" for payment in another column. Thanks, Peter |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an aging report
See if this example is gets you pointed in the right direction:
A1: DueDate B1: InvAmt C1: 30_Days D1: 60_Days E1: 90_Days F1: Over_90 A2: (some date) B2: (an invoice amount) C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"") D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"") E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"") F2: =IF(TRUNC(($A2-TODAY())/30)=3,$B2,"") (copy the formulas in C2 thru F2 down as far as you need) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: Hi, I'm trying to create an Accounts Receivable "aging report", using Excel 2003. I've got a field (a date field), that provides the date of invoice. I'd like then to add 30, 60, or 90 days from that date, and call that the "Due Date" for payment in another column. Thanks, Peter |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an aging report
"Peo Sjoblom" wrote: Excel dates are just numbers so if your invoice is in a date format that excel recognizes just add =A1+30 =A1+60 and do on where A1 holds the date -- Regards, Peo Sjoblom (No private emails please) "PSikes" wrote in message ... Hi, I'm trying to create an Accounts Receivable "aging report", using Excel 2003. I've got a field (a date field), that provides the date of invoice. I'd like then to add 30, 60, or 90 days from that date, and call that the "Due Date" for payment in another column. Thanks, Peter Dang, that was easy. Thanks Peo. Peter |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an aging report
"Ron Coderre" wrote: See if this example is gets you pointed in the right direction: A1: DueDate B1: InvAmt C1: 30_Days D1: 60_Days E1: 90_Days F1: Over_90 A2: (some date) B2: (an invoice amount) C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"") D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"") E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"") F2: =IF(TRUNC(($A2-TODAY())/30)=3,$B2,"") (copy the formulas in C2 thru F2 down as far as you need) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: Hi, I'm trying to create an Accounts Receivable "aging report", using Excel 2003. I've got a field (a date field), that provides the date of invoice. I'd like then to add 30, 60, or 90 days from that date, and call that the "Due Date" for payment in another column. Thanks, Peter Excellent! Thanks Ron. If I set up my entry worksheet, I can create a little Pivot table that'll key off of all 3 (30, 60, 90) categories -- just like the more expensive accounting programs do. Thanks much, Peter |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an aging report
Peter,
Would you mind sharing your pivot table and entry worksheet I am trying to do this also. Thanks Robert "PSikes" wrote: "Ron Coderre" wrote: See if this example is gets you pointed in the right direction: A1: DueDate B1: InvAmt C1: 30_Days D1: 60_Days E1: 90_Days F1: Over_90 A2: (some date) B2: (an invoice amount) C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"") D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"") E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"") F2: =IF(TRUNC(($A2-TODAY())/30)=3,$B2,"") (copy the formulas in C2 thru F2 down as far as you need) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: Hi, I'm trying to create an Accounts Receivable "aging report", using Excel 2003. I've got a field (a date field), that provides the date of invoice. I'd like then to add 30, 60, or 90 days from that date, and call that the "Due Date" for payment in another column. Thanks, Peter Excellent! Thanks Ron. If I set up my entry worksheet, I can create a little Pivot table that'll key off of all 3 (30, 60, 90) categories -- just like the more expensive accounting programs do. Thanks much, Peter |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an aging report
Hi Robert,
Sure, be glad to. Give me a few. I'm redesigning the entry worksheet, mostly to get rid of a lot of unecessary fat. A digitally appropriate New Year's resolution, don't you think? Peter "Robert" wrote: Peter, Would you mind sharing your pivot table and entry worksheet I am trying to do this also. Thanks Robert "PSikes" wrote: "Ron Coderre" wrote: See if this example is gets you pointed in the right direction: A1: DueDate B1: InvAmt C1: 30_Days D1: 60_Days E1: 90_Days F1: Over_90 A2: (some date) B2: (an invoice amount) C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"") D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"") E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"") F2: =IF(TRUNC(($A2-TODAY())/30)=3,$B2,"") (copy the formulas in C2 thru F2 down as far as you need) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: Hi, I'm trying to create an Accounts Receivable "aging report", using Excel 2003. I've got a field (a date field), that provides the date of invoice. I'd like then to add 30, 60, or 90 days from that date, and call that the "Due Date" for payment in another column. Thanks, Peter Excellent! Thanks Ron. If I set up my entry worksheet, I can create a little Pivot table that'll key off of all 3 (30, 60, 90) categories -- just like the more expensive accounting programs do. Thanks much, Peter |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an aging report
It certainly would..... :)
Robert "PSikes" wrote: Hi Robert, Sure, be glad to. Give me a few. I'm redesigning the entry worksheet, mostly to get rid of a lot of unecessary fat. A digitally appropriate New Year's resolution, don't you think? Peter "Robert" wrote: Peter, Would you mind sharing your pivot table and entry worksheet I am trying to do this also. Thanks Robert "PSikes" wrote: "Ron Coderre" wrote: See if this example is gets you pointed in the right direction: A1: DueDate B1: InvAmt C1: 30_Days D1: 60_Days E1: 90_Days F1: Over_90 A2: (some date) B2: (an invoice amount) C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"") D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"") E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"") F2: =IF(TRUNC(($A2-TODAY())/30)=3,$B2,"") (copy the formulas in C2 thru F2 down as far as you need) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: Hi, I'm trying to create an Accounts Receivable "aging report", using Excel 2003. I've got a field (a date field), that provides the date of invoice. I'd like then to add 30, 60, or 90 days from that date, and call that the "Due Date" for payment in another column. Thanks, Peter Excellent! Thanks Ron. If I set up my entry worksheet, I can create a little Pivot table that'll key off of all 3 (30, 60, 90) categories -- just like the more expensive accounting programs do. Thanks much, Peter |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an aging report
Ron,
I've had a chance to try out your sample. If I understand the script correctly, I'll not only get a clear snapshot of overdue collections within the 30,60,and 90 day timeframes, but I can total them up too! For some reason, the formula doesn't seem to work over the 2005-2006 window though. If I date an Due date back in November 1 2005 (not so unusual in today's environment), for instance -- using the TODAY date in the formula. The invoice amount doesn't show up in the 60 day window that I'd expect to see it. Did I do something wrong? Peter "Ron Coderre" wrote: See if this example is gets you pointed in the right direction: A1: DueDate B1: InvAmt C1: 30_Days D1: 60_Days E1: 90_Days F1: Over_90 A2: (some date) B2: (an invoice amount) C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"") D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"") E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"") F2: =IF(TRUNC(($A2-TODAY())/30)=3,$B2,"") (copy the formulas in C2 thru F2 down as far as you need) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: Hi, I'm trying to create an Accounts Receivable "aging report", using Excel 2003. I've got a field (a date field), that provides the date of invoice. I'd like then to add 30, 60, or 90 days from that date, and call that the "Due Date" for payment in another column. Thanks, Peter |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an aging report
Robert,
Waiting an answer on my last post. The program that Ron suggested works, but apparently not over the span of a year. Peter "Robert" wrote: Peter, Would you mind sharing your pivot table and entry worksheet I am trying to do this also. Thanks Robert "PSikes" wrote: "Ron Coderre" wrote: See if this example is gets you pointed in the right direction: A1: DueDate B1: InvAmt C1: 30_Days D1: 60_Days E1: 90_Days F1: Over_90 A2: (some date) B2: (an invoice amount) C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"") D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"") E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"") F2: =IF(TRUNC(($A2-TODAY())/30)=3,$B2,"") (copy the formulas in C2 thru F2 down as far as you need) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: Hi, I'm trying to create an Accounts Receivable "aging report", using Excel 2003. I've got a field (a date field), that provides the date of invoice. I'd like then to add 30, 60, or 90 days from that date, and call that the "Due Date" for payment in another column. Thanks, Peter Excellent! Thanks Ron. If I set up my entry worksheet, I can create a little Pivot table that'll key off of all 3 (30, 60, 90) categories -- just like the more expensive accounting programs do. Thanks much, Peter |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
creating an aging report
When I type this into the cell, an error message stating that there are too
many arguments is returned to me. "Ron Coderre" wrote: See if this example is gets you pointed in the right direction: A1: DueDate B1: InvAmt C1: 30_Days D1: 60_Days E1: 90_Days F1: Over_90 A2: (some date) B2: (an invoice amount) C2: =IF(TRUNC(($A2-TODAY())/30)=0,$B2,"") D2: =IF(TRUNC(($A2-TODAY())/30)=1,$B2,"") E2: =IF(TRUNC(($A2-TODAY())/30)=2,$B2,"") F2: =IF(TRUNC(($A2-TODAY())/30)=3,$B2,"") (copy the formulas in C2 thru F2 down as far as you need) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: Hi, I'm trying to create an Accounts Receivable "aging report", using Excel 2003. I've got a field (a date field), that provides the date of invoice. I'd like then to add 30, 60, or 90 days from that date, and call that the "Due Date" for payment in another column. Thanks, Peter |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with Aging Report | Excel Worksheet Functions | |||
| Creating excel report from DB2 table | Links and Linking in Excel | |||
| Linking worksheets after runnning report | Excel Discussion (Misc queries) | |||
| Microsoft Access Report into Excel Spreadsheet | Excel Discussion (Misc queries) | |||
| 30 Day Aging Report Using Conditional Formating | Excel Worksheet Functions | |||
Linear Mode
