Next quarter (period) formula

[deltaone]

Hi guys

Here is my first post

I am trying to develop a function that can retrieve a certain date in regards to certain criteria.

Let me explain: I have a client who needs to pay me.

The start date of the contract is 02/17/2012.
The end date of the contract is 02/18/2017.

During this period the client has to pay me every quarter. Therefore, he needs to pay me on 05/17/2012, 08/17/2012, 11/17/2012 and so on until the end date of the contract.

I would like to have in a certain cell that would have the nearest quarter payment. In other word, considering we are the 2/22/2013, the cell should show me 05/17/2013. If we were on 07/28/2012, the cell should show me 08/17/2012.

The formula should actually be generalized so that I can apply it to other customers that have a different payment frequency (ie: the client needs to pay me every 2 months, etc.)

The purpose of this would be to run a report at the end of each month that will have all the customers that need to pay me this month.

I have been trying to use MOD, DATE, MONTH, YEAR and DATES functions without any success…

It’s a real headache for me and I have been stuck on this for the past week, any help would be greatly appreciated!

Thanks a lot for the hand!


Cheers

 

[barry houdini]

Hello deltaone, welcome to MrExcel

If you have the start date of the contract in A2 this formula will give you the next payment date (not today) based on quarterly payments

=EDATE(A2,CEILING(DATEDIF(A2,TODAY(),"m")+1,3))

That doesn't take the end date into account, if you want to take that into account then this version will return the next payment date or text "No more payments", assuming B2 contains end date of contract

=IF(EDATE(A2,CEILING(DATEDIF(A2,TODAY(),"m")+1,3))>B2,"No more payments",EDATE(A2,CEILING(DATEDIF(A2,TODAY(),"m")+1,3)))

 

[deltaone]

Hi Barry

Thanks a lot for replying so fast.

I was actually working on that right now and this is what I found... It seems that it is working. With your references, I came up with:
=DATE(YEAR(TODAY()),MONTH(A1)+(CEILING(MONTH(TODAY()-A1),3)),DAY(A1))

I will have a look at your formula as well though because it seems to work as well I will get back with you

Thanks so much for the help

Cheers

 

[Peter_SSs]

I will have a look at your formula as well though because it seems to work as well

Though he is too modest to claim so himself, Barry is a well-known "date guru" in this forum so you would be well advised to have a good look at his formulas.

Dates can be very tricky, largely because of the varying number of days in a month.
In particular, have a look at the results of your formula and Barry's for a contract start date of, say, 30 November 2012. In my mind, Barry's formula results in a "better" answer given the current date is latish in February and the contract was started late in November.

 

[lrobbo314]

His psuedonym isn't Houdini for nothing.

 

[deltaone]

Peter,

Houdini is totally right, I did see a problem with my formula actually... His seems to work completely.

Quick question, I was trying to write his formula into a vba function. However, there seems to have a problem with the datediff function in VBA. For instance, if the date of the contract start 02/17/13 and today we are 05/11/13, the excel datedif function would return 2 but the vba datediff function returns 3, which basically cause the vba function I am writing to malfunction.

Is this "normal" problem?

Thanks for the help guys

 

[raj2206]

Hi deltaone,

Thanks for writing, this is a really interesting question/situation. Here's what I have come up:

In Column A (Start Date of Contract) - Put all the Start Dates
In Column B (End Date of Contract)- Put all the End Dates of the Contract
In Column C - (Today's Date) Write Today's Date with the Formula: =Today(), (the great thing about this is that it makes the Dates Dynamic, i.e. Every time/Day you open the workbook the Date automatically changes to today's Date)
In Column D - (Due Date) - Paste the following formula below:

=IF((OR(YEAR(A2)<>YEAR(C2),(MONTH(C2)-MONTH(A2))>3)),(IF(EDATE(A2,ROUNDUP(((((IF(YEAR(C2)-YEAR(A2)>0,YEAR(C2)-YEAR(A2),1)*12)-(MONTH(A2)-MONTH(C2)))+1)/3),0)*3)>B2,"Contract Expired "&TEXT(B2,"MM/DD/YYYY"),EDATE(A2,ROUNDUP(((((IF(YEAR(C2)-YEAR(A2)>0,YEAR(C2)-YEAR(A2),1)*12)-(MONTH(A2)-MONTH(C2)))+1)/3),0)*3))),"Payment Due From Next Quarter "&TEXT(EDATE(A2,3),"MM/DD/YYYY"))

I hope this will work fine and give you your desired results.

Please try and inform if there is any gap.

Thanks/ Raj

 

[Peter_SSs]

Raj

That is a mighty formula but it does have some "gaps" & you asked to be informed about them.
So here are a few examples:

Example 1
A2: 27 Nov 2012
B2: 27 Nov 2017
C2: 24 Feb 2013
D2: Your formula
Your formula returns the next payment date as 27 May 2013 when it would be 27 Feb 2013

Example 2 (one of the examples from post #1)
A3: 17 Feb 2012
B3: 18 Feb 2017
C3: 28 Jul 2012
D3: Your formula
Your formula returns the next payment date as 17 Aug 2013 when it should be 17 Aug 2012

Example 3
A4: 2 Aug 2012
B4: 2 Aug 2015
C4: 22 Nov 2012
D4: Your formula
Your formula returns "Payment Due From Next Quarter 11/02/2012" but in this example the 2 Nov 2012 payment should have already been made 20 days ago and the next payment would be due on 2 Feb 2013

 

[raj2206]

Hi Peter,

Thanks for highlighting!

I think there would be some minor gaps, let me work on it again and get back. This one seems to be a great problem.

Thanks Again/ Raj

 

[Peter_SSs]

.. let me work on it again and get back. This one seems to be a great problem.

Have you had a look at barry houdini's formulas in post #2?