formulas spanning overlapping years

[PSikes]

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
> >

[Ron Coderre]

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
> > >
>

[PSikes]

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
> > > >
> >

[Ron Coderre]

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
> > > > >
> > >