Phaedrus74
New Member
- Joined
- Jun 20, 2011
- Messages
- 1
Hi, I'm trying to write a formula to calculate when an exact date will occur in the future. I have a start date (say 21/12/1993) for an event which recurrs periodically (say every 5 years). The last event was therefore 21/12/2008. I want the formula to calculate when the next event date is from a given date (say Today 20/06/2011).
I have written the following formula
=EDATE(TODAY(),-MOD(DATEDIF(EventStart,TODAY(),"m"),60))
My result is one month out, giving 20/01/2009.
I can correct this by adapting the formula to the following:
=EDATE(TODAY(),-(MOD(DATEDIF(EventStart,TODAY(),"m"),60)+1))+1
But is there a better way, or rather,could you explain why I have to add 1 month and 1 day to arrive at the correct answer?
Many thanks
I have written the following formula
=EDATE(TODAY(),-MOD(DATEDIF(EventStart,TODAY(),"m"),60))
My result is one month out, giving 20/01/2009.
I can correct this by adapting the formula to the following:
=EDATE(TODAY(),-(MOD(DATEDIF(EventStart,TODAY(),"m"),60)+1))+1
But is there a better way, or rather,could you explain why I have to add 1 month and 1 day to arrive at the correct answer?
Many thanks
