# 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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### 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...

Replies
2
Views
241
Replies
3
Views
83
Replies
5
Views
57
Replies
8
Views
416
Replies
0
Views
143