Calculate Next Due Date for Recurring Tasks

[gnaekel]

I am trying to calculate the 'next due' date for a set of recurring tasks (quarterly, semiannual, annual) based on the 'last done' date. The difficulty is that the task must remain on a set schedule. For example, if a quarterly task is designated to be performed in March, June, September and December (3,6,9,12) and if it done a month late or early, the 'next due' date needs to remain in the original frequency. I have worked with EDATE and CEILING but cannot figure out how to force the desired fixed schedule. Please see the sample data and formula so far below. Any help is greatly appreciated.

[TABLE="class: grid, ******* 200, align: center"]
<tbody>[TR]
[TD]last done[/TD]
[TD]frequency[/TD]
[TD]fixed month[/TD]
[TD]next due[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/1/15[/TD]
[TD]12[/TD]
[TD]9[/TD]
[TD]=EDATE(A2,CEILING(DATEDIF(A2,TODAY(),"m")+1,B2))[/TD]
[TD]annual[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/22/16[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]=EDATE(A2,CEILING(DATEDIF(A2,TODAY(),"m")+1,B2))[/TD]
[TD]quarterly[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Muhammad_Usman]

Try this one! =EDATE(C18,CEILING(DATEDIF(C18,C18,"m")+1,D18))

 

[gnaekel]

Hi Muhammad. Thank you very much for taking the time to respond. Unfortunately, using the 'last done' date twice in the DATEDIF function does not ensure that the tasks remain in the predefined schedule.

What I'm trying to do is designate a schedule with the 'fixed month' field. For example setting it equal to 9 and defining the frequency as 3 (quarterly), I want the task only to be due in March, June, September, December (3,6,9,12). That is why I thought the CEILING function would help round up.

The simple date function would be EDATE(A2, B2), where A2 contains 'last done' and B2 contains 'frequency'. The problem is that when a task is done late, say the next month, everything is pushed a month later. I want it to revert to being due in the next regularly scheduled month.

If you or anyone else has any ideas, I could really use some help.

Thanks!

 

[Bhetlouge]

Given the date 02/28/1995 and today's date (09/18/2017), how can I get the next premium date for annual, semi annual or quarterly without going through all previous payment dates?

 

[Tetra201]

Maybe

=EDATE(A1,CEILING(YEARFRAC(A1,A2),X)*12)

where X = 1 for annual, X = 0.5 for semiannual, and X = 0.25 for quarterly payments.