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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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
50,699
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
50,699
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
 

Forum statistics

Threads
1,141,930
Messages
5,709,400
Members
421,635
Latest member
mehdi hannechi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top