Next Due Date

jprlaw

New Member
Joined
Feb 9, 2003
Messages
11
Morning,

Just a quick one hopefully...

I am trying to return the next payment due date in a cell E2. in cell C2 i have the first payment date, and in cell d2 i have the frequency of payments (ie 7 days, 14 days etc).

Any ideas?

Any help is much appreciated

Jon
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Use the formula I have in D2

=DATE(YEAR(NOW()),MONTH(NOW()),DAY(B2)+C2)

Adjust as needed
Book1
ABCD
1TodayStart DateFrequencyNext
211/14/2005712/21/2005
Sheet1
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Hi Gibbs,

=DATE(YEAR(NOW()),MONTH(NOW()),DAY(B2)+C2)

Just to point out you've got a bit carried away with your use of now(), given the example start date of 14th November 2005 :)

& given that dates are just integers, in general:

(start_date)+(P*N)

suffices, where:

P = period length, and
N = number of periods

...so given that we're talking one period:

=(start_date)+(P*1)

=(start_date)+(P)
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Didn't mean to get carried away with it, it was just a 2 second, no thought option. The 11/14/2005 was a random date I picked to illustrate that based on today, and a payment due every 7 days, that the next payment due in the cycle would be 12/21/05

I meant to just point it to A1, which was going to have todays date. The idea was that the formula would show the next due date on file open or recalculate.

I agree with your logic completely, but I read the poster to want the next due date based on the frequency of payments due, based on their initial due date and the current date.

If he does not want to have an additional formula to calcualte the remaining periods, or number of periods gone by, my formula does what is necessary even if not as elegant.
 

Ronald Moore

Board Regular
Joined
Aug 22, 2005
Messages
101
To Gibbs: I agree with you that there is "more than one period of interest", but your formula is incorrect. With a start date of Nov 14 and a billing cycle of 7 days in your example, the billing dates would by Nov 21, Nov 28, Dec 5, Dec 12, Dec 19, etc. so your result should have been Dec 12. With the start date in C2, and the billing cycle length in days in D2, some possible formulas in E2 for the next billing date are:

=TODAY()+D2-MOD(TODAY()-C2,D2)

or

=C2+CEILING(TODAY()+1-C2,D2)

If the current date is itself a billing date, these formulas will return the following billing date, not the current date. To return the current date in this case, change the formulas to:

=TODAY()+MOD(C2-TODAY(),D2)

or

=C2+CEILING(TODAY()-C2,D2)
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Whoops! Thanks for pointing that out...late night, not enough coffee and a whole lot of brain fades...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,545
Messages
5,838,008
Members
430,526
Latest member
NiceGuyWithExcel2007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top