Recurring due date for annual event

[Nolander01]

I'll try to be as concise yet informative as possible. In the included attachment you will find a list of names in column A. These names have each have an event that is due at the end of his/her month, once annually. The due month for each person is listed in column B. Once that person completes the event, it is recorded in column C. The event will then not be due until the following year by the end of their due month.

I need column D to reflect the last day of their due month.

This date will change once the event is completed and recorded in column C. There are of course a few additional requirements that puts this out of my ability. They are listed below:

The person may complete the event at any time within three months of their due month. So if Tom's due month is September, he may complete the event anytime in July, August, or September. This would then need to reset the "Due by" date (column D).

If the person does not complete the event, the original "due by" date should not change until the event is completed.

Last note: this attachment is from Excel for Mac, The actual sheet this will be used on will be windows (so no worries about the formula changes for Mac).

Thanks in advance.







Recurring due date help.xlsx

[samba_ravi]

=DATE(YEAR(C2)+1,MONTH(B2)+1,0)
Try this in Cell "D2" and copy towards down

[Nolander01]

I like your suggestion, but unfortunately I found a few issues. Here are they are:

If the due month is January, and the person completes the event is December, it is not updating the "due by" column for the next year. With the attachment below, you'll notice Adam completed the event in December. Therefore his new "due by" date should reflect 31 Jan 2015 (not 2014).

Also (and this is an issue I can live with). The completion date should only change the "due by" date if it is within 3 months of the "due by" date. So, Nick having completed the event in February, should not change his "due by" date of 30-JUN-14. But again, this issue is not that important since the event is not scheduled until the person is within the three month window.

[samba_ravi]

=DATE(YEAR(C2)+IF(MONTH(B2)>=MONTH(C2),1,2)-IF(MONTH(B2)-MONTH(C2)>=3,1,0),MONTH(B2)+1,0)
try this

[Nolander01]

I am having trouble testing it on this computer (Mac). I think there are compatibility issues when it comes to formulas regarding dates with Mac software. I think it will work though. It looks right.

Thanks so much. Will mark as solved and provide positive feedback.