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
 

Some videos you may like

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

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,635
Members
412,334
Latest member
ExcelForLifeDontHate
Top