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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
Whoops! Thanks for pointing that out...late night, not enough coffee and a whole lot of brain fades...
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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
Back
Top