Find quarter, bi-annual, annual date formula

[alvbnp]

Is there any formula I can use to find the next quarter date, bi-annual date or annual date?

Sample data:

Date | Type
12/01/2006 | 4
26/07/2004 | 2
22/01/1995 | 1

12/01/2006 with 4 quarter, the next quarter date should be 12/10/2006
26/07/2004 with bi-annual, the next date should be 26/01/2007
22/01/1995 with annual, the next date should be 22/01/2007

 

[Doug.T]

I am confused by your dates but the simplest formula would be to use the =EDATE function

if your date 12/4/2006 in is cell A1 then
=EDATE(A1,3) would give you the date 3 months (1 quarter) from 12/4/2006
=EDATE(A1,6) would give you the date 6 months (bi-annual) from 12/4/2006

Note that EDATE is not functional in Excel by default. You must install the VBA Toolpak for it to work.
Click on Tools-AddIns-AnalysisToolpak and Tools-Addins-AnalysisToolPak-VBA. (you may have to close and re-open Excel after you do this).

 

[alvbnp]

Thanks for replying. But it's not really what I need. The date can just be any date back to previous few years. What I want is to look out for the next relevant date accordingly, either quarterly, bi-annually or annually. So basically, it would has to compare with today date.

Or this has to be done using macro?

 

[fairwinds]

Hi,

Try:

=MIN(IF(DATE(YEAR(A2),MONTH(A2)+ROW(INDIRECT("1:99"))*12/B2,DAY(A2))>=TODAY(),DATE(YEAR(A2),MONTH(A2)+ROW(INDIRECT("1:99"))*12/B2,DAY(A2))))

confirmed with Ctrl + shift

 

[Peter_SSs]

alvbnp

I am also not quite certain how you define next quarter or next bi-annual date, but see if this is on the right track. If not, please explain the expected results for the data given.

1. Formula in E2: =TODAY()
to record the current date.
2. Formula in C2 (copied down):
=EDATE(A2,CEILING(1+DATEDIF(A2,$E$2,"m"),12/B2))

Mr Excel.xls
	A	B	C	D	E	F
1	Date	Type	Result		Today
2	12/01/2006	4	12/10/2006		25/09/2006
3	26/07/2004	2	26/01/2007
4	22/01/1995	1	22/01/2007
5	26/09/2005	1	26/09/2006
6	25/09/2004	1	25/09/2007
7	24/09/2003	1	24/09/2007
8	15/05/2003	4	15/11/2006
9	28/08/2001	2	28/02/2007
10
Next Date

 

[alvbnp]

Thanks for helping. Both formula working pretty fine.

For the formula by Peter, if the date is a future date, it show #NUM! error.
eg: 13/09/2007, it suppose to just show 13/09/2007

I am thinking for another formula to get the most current date.

Using the same sample data as above:
12/01/2006 should show 12/07/2006
26/07/2004 should show 26/07/2006
22/01/1995 should show 22/01/2006

 

[alvbnp]

Hi,

Try:

=MIN(IF(DATE(YEAR(A2),MONTH(A2)+ROW(INDIRECT("1:99"))*12/B2,DAY(A2))>=TODAY(),DATE(YEAR(A2),MONTH(A2)+ROW(INDIRECT("1:99"))*12/B2,DAY(A2))))

confirmed with Ctrl + shift

Thanks fairwinds. It work quite well except it don't round up to the last day of the month.

eg:
31/08/2006 | 2
it show 03/03/2006, it suppose to be 28/02/2006.

Besides that, can you also help to provide another formula that find the last date, I mean the most recent passed date.

 

[Peter_SSs]

Thanks for helping. Both formula working pretty fine.

For the formula by Peter, if the date is a future date, it show #NUM! error.
eg: 13/09/2007, it suppose to just show 13/09/2007

I am thinking for another formula to get the most current date.

Using the same sample data as above:
12/01/2006 should show 12/07/2006
26/07/2004 should show 26/07/2006
22/01/1995 should show 22/01/2006

See if this is what you want:

1. C2 (copied down):
=IF(A2<$E$2,EDATE(A2,CEILING(1+DATEDIF(A2,$E$2,"m"),12/B2)),A2)

2. D2 (copied down):
=IF(A2<$E$2,EDATE(C2,-12/B2),A2)

Mr Excel.xls
	A	B	C	D	E	F
1	Date	Type	Next Date	Current Date	Today
2	12/01/2006	4	12/10/2006	12/07/2006	26/09/2006
3	26/07/2004	2	26/01/2007	26/07/2006
4	22/01/1995	1	22/01/2007	22/01/2006
5	26/09/2005	1	26/09/2007	26/09/2006
6	25/09/2004	1	25/09/2007	25/09/2006
7	24/09/2003	1	24/09/2007	24/09/2006
8	15/05/2003	4	15/11/2006	15/08/2006
9	28/08/2001	2	28/02/2007	28/08/2006
10	13/09/2007	2	13/09/2007	13/09/2007
11	31/08/2006	2	28/02/2007	28/08/2006
12
Next Date