How to determine Next Quarter if I know current quarter?

clawlan

Board Regular
Joined
Aug 9, 2010
Messages
62
I have a simple query that looks at today's date, references our corporate calendar, and displays the current fiscal quarter (eg. FY11-Q4). I am having trouble coming up with a way to determine the next quarter though.

The reason i am using these queries is because I then need to pass each of their results into a query that uses the quarter as a criteria.

So overall, user click form button "Run Current Quarter" and it would use the above query to determine the current quarter, then pass to the query. Then use can click "Run Next Quarter" and it would run a query and then pass this result to the query. Make sense?
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

revans

Well-known Member
Joined
Apr 5, 2010
Messages
576
I use functions like these to return quarter info. You'll have to tweak them to return info for whatever your quarters are.

Code:
Public Function GetStartOfQuarter(i As Long, y As Long) As Date
'return start of quarter i(1,2,3, or 4) for year (y)
  Dim temp As Long
  Select Case i
    Case 1: temp = CDate("1/1/" & y)
    Case 2: temp = CDate("4/1/" & y)
    Case 3: temp = CDate("7/1/" & y)
    Case 4: temp = CDate("10/1/" & y)
  End Select
  GetStartOfQuarter = temp
End Function
 
Public Function GetStartOfQuarter2(d As Date) As Date
'return start of quarter d exists within (1,2,3,4)
  Dim temp As Date
  temp = GetStartOfQuarter(DatePart("q", d), Year(d))
  GetStartOfQuarter2 = temp
End Function
 
Public Function GetEndOfQuarter(i As Long, y As Long) As Date
'return end of quarter i(1,2,3, or 4) for year (y)
  Dim temp As Date
  Select Case i
    Case 1: temp = CDate("3/31/" & y)
    Case 2: temp = CDate("6/30/" & y)
    Case 3: temp = CDate("9/30/" & y)
    Case 4: temp = CDate("12/31/" & y)
  End Select
  GetEndOfQuarter = temp
End Function
 
Public Function GetEndOfQuarter2(d As Date) As Date
'return end of quarter d exists within (1,2,3,4)
  Dim temp As Date
  temp = GetEndOfQuarter(DatePart("q", d), Year(d))
  GetEndOfQuarter2 = temp
End Function

Once you know what your quarter is you should be able to just add one, or something :)

hth,

Rich
 

Watch MrExcel Video

Forum statistics

Threads
1,109,517
Messages
5,529,305
Members
409,862
Latest member
lbisacca
Top