Calculate Next Due Date

[KenHet]

I'm sure it is easy but.....
Given a start date, today's date and a monthly interval, I want to calculate the next time an event should happen. For example, if the starte date is 1/15/2009 and today is 9/20/2011 and the monthly interval is 3, then the next due date should be something like 10/15/2011.

However, if the start date was 2/15/2009 and today is 9/20/2011 and the monthly interval is still 3, then the next due date would be 11/15/2011.

I need the formula to be flexible enough so that I can adjust the interval to typical yearly frequencies using whole numbers of months such as 1,2,4,6,and 12.

Thanks

 

[MAP1168]

=IF(DATE(YEAR(C4),ROUNDUP((MONTH(C4)-MONTH(A4))/B4,0)*B4+MONTH(A4),DAY(A4))>C4,DATE(YEAR(C4),ROUNDUP((MONTH(C4)-MONTH(A4))/B4,0)*B4+MONTH(A4),DAY(A4)),DATE(YEAR(C4),ROUNDUP((MONTH(C4)-MONTH(A4))/B4,0)*B4+B4+MONTH(A4),DAY(A4)))

I needed to modify this incase the next caclulated event was less than the current date.

 

[Joe4]

I created a User Defined Function that should do this. Where things get a little dicey is if you have a start date near the end of the month (day greater than 28) and the current day is near the end of the month (day greater than 28). That's because not all the month have the same number of days. I think I have added some special checks at the end of the code to account for that.
<o>

[COLOR=black][FONT=Verdana]<?xml:namespace prefix = o /><o:p>Function NextDueDate(StartDate As Date, MonthlyInterval As Integer) As Date</o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR] 
[COLOR=black][FONT=Verdana]<o:p>    Dim DueDate As Date
    Dim IntervalCounter As Integer
    Dim DateCheck1 As Date
    Dim DateCheck2 As Date
    Dim DateCheck3 As Date
    
'   Loop through to count the number of internals it takes to exceed current date
    DueDate = StartDate
    Do Until DueDate > Date
        DueDate = DateAdd("m", MonthlyInterval, DueDate)
        IntervalCounter = IntervalCounter + 1
    Loop
    
'   Based on IntervalCounter value, re-calculate dates for 1 interval prior and after to catch and month-end adjustments
    If IntervalCounter >= 1 Then
        DateCheck1 = DateAdd("m", (IntervalCounter - 1) * MonthlyInterval, StartDate)
    End If
    DateCheck2 = DateAdd("m", IntervalCounter * MonthlyInterval, StartDate)
    DateCheck3 = DateAdd("m", (IntervalCounter + 1) * MonthlyInterval, StartDate)
    
'   Determine the first future date to return
    If DateCheck1 > Date Then
        NextDueDate = DateCheck1
    Else
        If DateCheck2 > Date Then
            NextDueDate = DateCheck2
        Else
            NextDueDate = DateCheck3
        End If
    End If
            
End Function</o:p>[/FONT][/COLOR]

So you would use this like you use any other Excel function, in the format:
=NextDueDate(Start Date,Monthy Interval)

So you can either use a cell reference or straight date value, i.e.
=NextDueDate("2/15/2010",3)
or
=NextDueDate(A1,3)</o>

 

[KenHet]

Joe4 - thanks - seems to work like a charm. For my use what I need as an output is just the month portion of the date, which I can do easily. My start dates can all be "tweaked" to ensure they are not past the 28th.

Thanks again and case closedray:

 

[barry houdini]

This formula should also do what you want

=EDATE(A2,CEILING(DATEDIF(A2,TODAY(),"m")+1,B2))

assuming start date in A2 and monthly interval in B2

 

[KenHet]

Thanks again - it appears to work also. Very slick. The one thing that I noticed is that if the Start Date is greater than today it results in an error. I know I can toss in some IF statements to perform some tests but that ends up resulting in a very long formula. This spreadsheet with this functionality will end up in the hands of other users so a user defined function with some simple arguments makes it a bit cleaner and hides what is happening. Thakns though as it is pretty neat.

 

[skillet]

So if you wanted to do a fixed date built into the formula you woudl do something like this for every other month
=EDATE((DATE(2017,12,15)),CEILING(DATEDIF((DATE(2017,12,15)),TODAY(),"m")+1,2))

Or this for every quarter
=EDATE((DATE(2017,12,15)),CEILING(DATEDIF((DATE(2017,12,15)),TODAY(),"m")+1,3))