Date functions

[Shree1]

Hi, I have the start date of an event (say 20/03/2012) and it occurs every 6 months. How do I get the next date of occurrence, in this year in excel as well as the date the event last occurred.

 

[par60056]

assuming date is in A2:
=date(year(a2),month(a2)+6,day(a2))

 

[Special-K99]

Next date
=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))

Last date
=DATE(YEAR(A1),MONTH(A1)-6,DAY(A1))

where A1 is the event date

 

[Momentman]

like this

Excel 2010
	A	B	C
1	DATE	6 MONTHS AFTER	6 MONTHS PRIOR
2	20/03/2012	20/09/2012	20/09/2011
Sheet1 (7)

Cell Formulas
Range	Formula
B2	=DATE(YEAR(A2),MONTH(A2)+6,DAY(A2))
C2	=DATE(YEAR(A2),MONTH(A2)-6,DAY(A2))

 

[Shree1]

Hi, I want next occurrence compared to today's date, that is what is the next occurrence (20/03/2015|) and what was the last occurrence (20/03/2014). I need them to be in business days

 

[Momentman]

Hi, I want next occurrence compared to today's date, that is what is the next occurrence (20/03/2015|) and what was the last occurrence (20/03/2014). I need them to be in business days

Am sorry, but i don't quite follow....Today is 25th first of all and not 20th Using 25/09/2012, what would be your last occurrence and what would be your next occurrence?

And when you say business days? what do you mean exactly? that the answer must not be a weekend or what?

 

[Shree1]

So, I have the start date of an event, that is, the 20/03/2012, and it occurs every 6 months till 5 years say. Is there a function that will give me when it occurred last, compared to today's date, and when it will occur again, compared to today's date. In my example, the last occurrence of the event compared to today would be 20/06/2014 and the next occurrence will be 20/03/2015. And yes, by business days, I mean excluding weekends and bank holidays. Hope this makes sense!

 

[barry houdini]

You could try a formula like this to get the last occurence

=EDATE(A2,FLOOR(DATEDIF(A2,TODAY(),"m"),6))

assuming your start date in A2......and for next occurence

=EDATE(A2,FLOOR(DATEDIF(A2,TODAY(),"m"),6)+6)

If you always want the result to be a weekday try these versions

=WORKDAY(EDATE(A2,FLOOR(DATEDIF(A2,TODAY(),"m"),6))-1,1)

....and

=WORKDAY(EDATE(A2,FLOOR(DATEDIF(A2,TODAY(),"m"),6)+6)-1,1)

You might have to check what results you get when today is exactly on the "anniversary" date, do you want today to count as the last one....or the next one?

 

[Shree1]

Works like a charm, many thanks!