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?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,626
Messages
5,838,438
Members
430,548
Latest member
hh_dh2001

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