# How to determine Next Quarter if I know current quarter?

#### clawlan

##### Board Regular
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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

Replies
2
Views
636
Replies
3
Views
1K
Replies
1
Views
662
Replies
2
Views
3K
Replies
5
Views
716

1,221,493
Messages
6,160,139
Members
451,624
Latest member
TheWes

### 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.

### Which adblocker are you using?

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

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