| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas spanning overlapping years
I've tried the simple formula in the thread found below, and discovered that
if I use an initial date in 2005, and a "due" date in 2006, it doesn't seem to work. Can anybody tell me why? I must be doing something wrong. ( I'm reposting to both the programming and worksheet function boards, because my post below didn't seem to get any attention. Thanks, Peter ---------------------------------------------------------------- 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formulas spanning overlapping years
Hi, Peter
Sorry about the confusion...I thought you were calculating how soon the payments would be due. What you really wanted was an aging, right? Try this C2: =IF(TRUNC((TODAY()-$A2)/30)=0,$B2,"") D2: =IF(TRUNC((TODAY()-$A2)/30)=1,$B2,"") E2: =IF(TRUNC((TODAY()-$A2)/30)=2,$B2,"") F2: =IF(TRUNC((TODAY()-$A2)/30)2,$B2,"") Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: I've tried the simple formula in the thread found below, and discovered that if I use an initial date in 2005, and a "due" date in 2006, it doesn't seem to work. Can anybody tell me why? I must be doing something wrong. ( I'm reposting to both the programming and worksheet function boards, because my post below didn't seem to get any attention. Thanks, Peter ---------------------------------------------------------------- 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formulas spanning overlapping years
Hi Ron,
I'm sorry for this repost, though your modification is a better way to display the aging of the $amount due. For some reason, I wasn't able to get the formulas to work, when I backdated into 2005. I re-entered the formulas you recommended, and now it seems to work! Thanks so much for your patience. I'm learning slowly. BTW, what does the TRUNC command do in the function? Best, Peter "Ron Coderre" wrote: Hi, Peter Sorry about the confusion...I thought you were calculating how soon the payments would be due. What you really wanted was an aging, right? Try this C2: =IF(TRUNC((TODAY()-$A2)/30)=0,$B2,"") D2: =IF(TRUNC((TODAY()-$A2)/30)=1,$B2,"") E2: =IF(TRUNC((TODAY()-$A2)/30)=2,$B2,"") F2: =IF(TRUNC((TODAY()-$A2)/30)2,$B2,"") Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: I've tried the simple formula in the thread found below, and discovered that if I use an initial date in 2005, and a "due" date in 2006, it doesn't seem to work. Can anybody tell me why? I must be doing something wrong. ( I'm reposting to both the programming and worksheet function boards, because my post below didn't seem to get any attention. Thanks, Peter ---------------------------------------------------------------- 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formulas spanning overlapping years
I'm glad that worked for you.
The TRUNC function truncates everything to the right of the decimal point. Since Days Outstanding/30 can return non-integers, TRUNC ensures only integer values. It's easier to compare the results to a single digit (0,1,2 etc) than a range (0-29, 30-59, etc). Example: If an invoice is only 15 days old, 15/30 returns 0.5, but that invoice is in the 0 to 30 range, right? TRUNC(0.5) returns 0 Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: Hi Ron, I'm sorry for this repost, though your modification is a better way to display the aging of the $amount due. For some reason, I wasn't able to get the formulas to work, when I backdated into 2005. I re-entered the formulas you recommended, and now it seems to work! Thanks so much for your patience. I'm learning slowly. BTW, what does the TRUNC command do in the function? Best, Peter "Ron Coderre" wrote: Hi, Peter Sorry about the confusion...I thought you were calculating how soon the payments would be due. What you really wanted was an aging, right? Try this C2: =IF(TRUNC((TODAY()-$A2)/30)=0,$B2,"") D2: =IF(TRUNC((TODAY()-$A2)/30)=1,$B2,"") E2: =IF(TRUNC((TODAY()-$A2)/30)=2,$B2,"") F2: =IF(TRUNC((TODAY()-$A2)/30)2,$B2,"") Does that help? *********** Regards, Ron XL2002, WinXP-Pro "PSikes" wrote: I've tried the simple formula in the thread found below, and discovered that if I use an initial date in 2005, and a "due" date in 2006, it doesn't seem to work. Can anybody tell me why? I must be doing something wrong. ( I'm reposting to both the programming and worksheet function boards, because my post below didn't seem to get any attention. Thanks, Peter ---------------------------------------------------------------- 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif formulas change after doing a sort | Excel Worksheet Functions | |||
| Cant find & delete invalid formulas or links | Excel Discussion (Misc queries) | |||
| Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
| Problem with named formula's | Excel Worksheet Functions | |||
| calculating formulas for all workbooks in a folder | Excel Worksheet Functions | |||
Linear Mode
