PDA

View Full Version : Calculating installments left



Sir Babydum GBE
06-06-2008, 11:04 AM
Hi I need to calculate installments between two dates.

Suppose A1 contains 10/01/2008 and B1 contains 08/10/2008

I want, firstly, in C1 to calculate monthly installments between the two. The answer i need to return is 9 because we take A1 to mean it's the first installment (so the 10th of each month is the due date) and October 10 falls after the second date (the 8th of October). So the customer needs to have paid his bill in full from January to September.

in C2 I need the number of fortnightly installments using the same logic

and in C3 the same again but weekly installments.

In the latter two instances the deciding factor is the weekday. So if Jan 10 was a thursday and October 8 were a wednesday, then the final installment would have to be whichever thursday was before the closing date.

I'm getting annoyed with this one so if you could help that would be fab.

grichey
06-06-2008, 11:07 AM
Will you post an example .xls with the desired answers just hard coded?

grichey
06-06-2008, 11:16 AM
Try this maybe.

note: dates are currently in yank format

Bob Phillips
06-06-2008, 12:56 PM
C1: =N(MONTH(A2)-MONTH(A1))+(A2>A1)

C2: =INT((A2-A1)/14)

C3: =INT((A2-A1)/7)

Sir Babydum GBE
09-28-2008, 05:11 PM
Forgive my rudeness in not replying sooner. There was a rather fast paced turn of events at the time of writing and I ended up leaving the company I was working for due to a department restructure.

For a while I didn't have internet access at home, and the reason I asked the question was no longer relevant to me.

That said, it was rude to not reply. So... sorry and thanks, of course, for the help.

Bob Phillips
09-29-2008, 12:04 AM
So what are you doing now then?

Aussiebear
09-29-2008, 01:38 AM
Breathing..... maybe?

Sir Babydum GBE
09-30-2008, 11:20 AM
So what are you doing now then?Well yes, I am breathing (for now) Aussiebear, but I think Bob had a more specific answer in mind that had a greater corelation with the statement I made about having left my job.

The answer, Bob, is "nothing". I'm, as they fashionably like to call it, "between jobs". So if any of you regulars out there know of any jobs going in the vicinity of Cardiff feel free to send me a PM. ("Private Message", not "Prime Minister". I'm really not in the mood to speak to him again this week. - oh, and not "Post Mortem" either - I'm not quite there yet)