Find quarter, bi-annual, annual date formula

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80
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

Board Regular
Joined
Jun 26, 2006
Messages
180
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEF
1DateTypeResultToday
212/01/2006412/10/200625/09/2006
326/07/2004226/01/2007
422/01/1995122/01/2007
526/09/2005126/09/2006
625/09/2004125/09/2007
724/09/2003124/09/2007
815/05/2003415/11/2006
928/08/2001228/02/2007
10
Next Date
 

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180
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

Board Regular
Joined
Jun 26, 2006
Messages
180
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,837
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1DateTypeNext DateCurrent DateToday
212/01/2006412/10/200612/07/200626/09/2006
326/07/2004226/01/200726/07/2006
422/01/1995122/01/200722/01/2006
526/09/2005126/09/200726/09/2006
625/09/2004125/09/200725/09/2006
724/09/2003124/09/200724/09/2006
815/05/2003415/11/200615/08/2006
928/08/2001228/02/200728/08/2006
1013/09/2007213/09/200713/09/2007
1131/08/2006228/02/200728/08/2006
12
Next Date
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,145
Members
410,666
Latest member
Al3cs
Top