# Next Due Date

#### jprlaw

##### New Member
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
Use the formula I have in D2

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

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

##### MrExcel MVP
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
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
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
Whoops! Thanks for pointing that out...late night, not enough coffee and a whole lot of brain fades...

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

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?

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