Help with not-so-easy Excel Problem 2

[dianemarie]

Hello, I have this data in a worksheet:

Debtor Debt DateIssued YrsToPay DueOnAnniversary

John Doe $500 1/1/03 4 $125
John Doe $1000 1/1/02 5 $200
Mary Smith $500 5/3/03 5 $100
etc

(DueOnAnniversary is simply the debt divided by number of years to pay.)

What I would like to be able to do is enter a date and debtor name, and have Excel calculate that debtor's obligation as of the date entered. The first payment is not due until the first anniversary. So if I entered today's date, John Doe would be obligated for $200 only. (The first anniversary date has passed for the $1000 debt, so he would be obligated for $200, but because the $500 debt has not yet reached the anniversary, there is no payment due. However, if I put in a date of 1/1/04, then John Doe would owe $525 (two payments from the $1000 debt and one payment from the $500 debt). Can Excel handle this or would it be better handled in Access? Thank you for any help or suggestions.

 

[SkipVought]

dianemarie,

I added this formula to the next column, with heading DueNow (using named ranges)...

=IF(TODAY()-365.25<DateIssued,0,DueOnAnniversary)

Then using the Subtotals Wizard, sum DueNow for each Debtor.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[dianemarie]

Hi Skip, this works if only one anniversary has passed. However, if two anniversaries have passed, then I need to multiply DueOnAnniversary by two...and if three have passed, then I need to multiply by three, etc. I'm going to work on it and see if I can expand what you've given me so far. I'll be checking back in case you pull some more magic out of your hat before I get it working. Thanks Skip.

 

[dianemarie]

Hi Skip, this is how I did it, but there is probably a more efficient way to get it. I added a column for NumAnniv (number of anniversaries) and used this formula:
=ROUNDUP((TODAY()-DateIssued)/365.25,0)

Then I revised the formula you gave me to:
=IF(TODAY()-365.25<DateIssued,0,DueOnAnniversary*NumAnniv)

One question, does the .25 in 365.25 allow for leap years?

 

[SkipVought]

365.25 conpensates for leap years but does not accout for any particular leap year. There is probably a way to do that that I don't know about.

As far as the number of anniversarys, I'd play around with INT((TODAY()-365.25)/365.25)*DueOnAnniversary

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Paul666]

The theory behind leap years is:

Years divisible by 4 are Leap Years (i.e. 1996 was)

However, most years divisible by 100 are NOT! (1900 was not)

Unless they are also divisible by 400 (2000 was)

Now this logic can simply be added into the equation by using a nested IF statement to check the above 3 criteria.

 

[dianemarie]

Interesting Paul. Thank you for that.