I have a query that puts dates into quarters ofr me. The problem is that it puts it into jan-mar as quarter one. I need jul-sep of 2008 to be q1 2009 and jan-mar to be q3 2009. Any ideas?
-
-
You can use the combination of a Query with a Calculated Field, that returns the proper Quarter via a Public Function. I used the Orders Table of the Northwind Database for this demo:- Query with Calculated Field:
Code:SELECT Orders.ShippedDate, fCalcQuarters([ShippedDate]) AS Quarter FROM Orders WHERE (((Orders.ShippedDate) Between #6/1/1997# And #5/31/1998#)) ORDER BY fCalcQuarters([ShippedDate]);
- Function Definition:
Code:Public Function fCalcQuarters(dteShipDate As Date) If dteShipDate >= #6/1/1997# And dteShipDate <= #8/31/1997# Then fCalcQuarters = "Q1" ElseIf dteShipDate >= #9/1/1997# And dteShipDate <= #11/30/1997# Then fCalcQuarters = "Q2" ElseIf dteShipDate >= #12/1/1997# And dteShipDate <= #2/28/1998# Then fCalcQuarters = "Q3" ElseIf dteShipDate >= #3/1/1998# And dteShipDate <= #5/30/1998# Then fCalcQuarters = "Q4" Else fCalcQuarters = Null End If End Function
- Sample Output:
Code:Shipped Date Quarter 31-Jul-1997 Q1 18-Jul-1997 Q1 07-Aug-1997 Q1 06-Aug-1997 Q1 06-Aug-1997 Q1 05-Aug-1997 Q1 04-Aug-1997 Q1 01-Aug-1997 Q1 01-Aug-1997 Q1 01-Aug-1997 Q1 08-Aug-1997 Q1 01-Aug-1997 Q1 11-Aug-1997 Q1 30-Jul-1997 Q1 29-Jul-1997 Q1 29-Jul-1997 Q1 25-Jul-1997 Q1 25-Jul-1997 Q1 22-Jul-1997 Q1 22-Jul-1997 Q1 21-Jul-1997 Q1 21-Jul-1997 Q1 18-Jul-1997 Q1 01-Aug-1997 Q1 20-Aug-1997 Q1 02-Jun-1997 Q1 29-Aug-1997 Q1 28-Aug-1997 Q1 27-Aug-1997 Q1 26-Aug-1997 Q1 26-Aug-1997 Q1 26-Aug-1997 Q1 21-Aug-1997 Q1 21-Aug-1997 Q1 08-Aug-1997 Q1 20-Aug-1997 Q1 12-Aug-1997 Q1 20-Aug-1997 Q1 19-Aug-1997 Q1 19-Aug-1997 Q1 19-Aug-1997 Q1 18-Aug-1997 Q1 15-Aug-1997 Q1 14-Aug-1997 Q1 14-Aug-1997 Q1 12-Aug-1997 Q1 11-Aug-1997 Q1 21-Aug-1997 Q1 06-Jun-1997 Q1 20-Jun-1997 Q1 19-Jun-1997 Q1 18-Jun-1997 Q1 18-Jun-1997 Q1 17-Jun-1997 Q1 16-Jun-1997 Q1 13-Jun-1997 Q1 13-Jun-1997 Q1 13-Jun-1997 Q1 24-Jun-1997 Q1 09-Jun-1997 Q1 12-Jun-1997 Q1 06-Jun-1997 Q1 06-Jun-1997 Q1 06-Jun-1997 Q1 05-Jun-1997 Q1 05-Jun-1997 Q1 04-Jun-1997 Q1 03-Jun-1997 Q1 02-Jun-1997 Q1 16-Jul-1997 Q1 13-Aug-1997 Q1 10-Jun-1997 Q1 14-Jul-1997 Q1 16-Jul-1997 Q1 09-Jun-1997 Q1 16-Jul-1997 Q1 25-Jun-1997 Q1 14-Jul-1997 Q1 14-Jul-1997 Q1 14-Jul-1997 Q1 11-Jul-1997 Q1 10-Jul-1997 Q1 10-Jul-1997 Q1 09-Jul-1997 Q1 09-Jul-1997 Q1 09-Jul-1997 Q1 30-Jun-1997 Q1 04-Jul-1997 Q1 26-Jun-1997 Q1 30-Jun-1997 Q1 30-Jun-1997 Q1 30-Jun-1997 Q1 01-Jul-1997 Q1 04-Jul-1997 Q1 04-Jul-1997 Q1 04-Jul-1997 Q1 02-Jul-1997 Q1 05-Nov-1997 Q2 30-Oct-1997 Q2 31-Oct-1997 Q2 31-Oct-1997 Q2 04-Nov-1997 Q2 07-Nov-1997 Q2 05-Nov-1997 Q2 05-Nov-1997 Q2 07-Nov-1997 Q2 29-Oct-1997 Q2 23-Oct-1997 Q2 05-Nov-1997 Q2 05-Nov-1997 Q2 29-Oct-1997 Q2 29-Oct-1997 Q2 29-Oct-1997 Q2 27-Oct-1997 Q2 21-Oct-1997 Q2 24-Oct-1997 Q2 23-Oct-1997 Q2 22-Oct-1997 Q2 21-Oct-1997 Q2 17-Oct-1997 Q2 20-Oct-1997 Q2 28-Nov-1997 Q2 10-Nov-1997 Q2 27-Oct-1997 Q2 21-Nov-1997 Q2 26-Sep-1997 Q2 16-Oct-1997 Q2 28-Nov-1997 Q2 28-Nov-1997 Q2 27-Nov-1997 Q2 27-Nov-1997 Q2 26-Nov-1997 Q2 25-Nov-1997 Q2 25-Nov-1997 Q2 24-Nov-1997 Q2 24-Nov-1997 Q2 27-Nov-1997 Q2 21-Nov-1997 Q2 11-Nov-1997 Q2 21-Nov-1997 Q2 20-Nov-1997 Q2 18-Nov-1997 Q2 18-Nov-1997 Q2 18-Nov-1997 Q2 18-Nov-1997 Q2 18-Nov-1997 Q2 17-Nov-1997 Q2 14-Nov-1997 Q2 14-Nov-1997 Q2 14-Nov-1997 Q2 12-Nov-1997 Q2 21-Nov-1997 Q2 10-Sep-1997 Q2 22-Sep-1997 Q2 19-Sep-1997 Q2 19-Sep-1997 Q2 19-Sep-1997 Q2 19-Sep-1997 Q2 18-Sep-1997 Q2 18-Sep-1997 Q2 17-Sep-1997 Q2 15-Sep-1997 Q2 15-Sep-1997 Q2 11-Sep-1997 Q2 22-Sep-1997 Q2 11-Sep-1997 Q2 03-Sep-1997 Q2 10-Sep-1997 Q2 09-Sep-1997 Q2 08-Sep-1997 Q2 08-Sep-1997 Q2 05-Sep-1997 Q2 03-Sep-1997 Q2 03-Sep-1997 Q2 16-Oct-1997 Q2 02-Sep-1997 Q2 26-Sep-1997 Q2 01-Sep-1997 Q2 01-Sep-1997 Q2 11-Sep-1997 Q2 14-Oct-1997 Q2 02-Sep-1997 Q2 23-Sep-1997 Q2 14-Oct-1997 Q2 14-Oct-1997 Q2 13-Oct-1997 Q2 13-Oct-1997 Q2 10-Oct-1997 Q2 09-Oct-1997 Q2 08-Oct-1997 Q2 07-Oct-1997 Q2 07-Oct-1997 Q2 03-Oct-1997 Q2 03-Oct-1997 Q2 29-Sep-1997 Q2 24-Sep-1997 Q2 15-Oct-1997 Q2 03-Oct-1997 Q2 23-Sep-1997 Q2 15-Oct-1997 Q2 30-Sep-1997 Q2 30-Sep-1997 Q2 30-Sep-1997 Q2 30-Sep-1997 Q2 01-Oct-1997 Q2 01-Oct-1997 Q2 26-Sep-1997 Q2 31-Dec-1997 Q3 26-Dec-1997 Q3 26-Dec-1997 Q3 26-Dec-1997 Q3 25-Dec-1997 Q3 24-Dec-1997 Q3 23-Dec-1997 Q3 05-Jan-1998 Q3 22-Dec-1997 Q3 22-Dec-1997 Q3 24-Dec-1997 Q3 31-Dec-1997 Q3 31-Dec-1997 Q3 01-Jan-1998 Q3 02-Jan-1998 Q3 02-Jan-1998 Q3 05-Jan-1998 Q3 05-Jan-1998 Q3 05-Jan-1998 Q3 05-Jan-1998 Q3 19-Dec-1997 Q3 09-Dec-1997 Q3 06-Jan-1998 Q3 02-Jan-1998 Q3 10-Dec-1997 Q3 07-Jan-1998 Q3 07-Jan-1998 Q3 02-Dec-1997 Q3 03-Dec-1997 Q3 04-Dec-1997 Q3 05-Dec-1997 Q3 05-Dec-1997 Q3 08-Dec-1997 Q3 08-Dec-1997 Q3 08-Dec-1997 Q3 12-Dec-1997 Q3 09-Dec-1997 Q3 19-Dec-1997 Q3 12-Dec-1997 Q3 12-Dec-1997 Q3 15-Dec-1997 Q3 15-Dec-1997 Q3 15-Dec-1997 Q3 16-Dec-1997 Q3 17-Dec-1997 Q3 18-Dec-1997 Q3 19-Dec-1997 Q3 19-Dec-1997 Q3 09-Dec-1997 Q3 09-Feb-1998 Q3 13-Feb-1998 Q3 12-Feb-1998 Q3 12-Feb-1998 Q3 12-Feb-1998 Q3 12-Feb-1998 Q3 12-Feb-1998 Q3 11-Feb-1998 Q3 11-Feb-1998 Q3 10-Feb-1998 Q3 04-Feb-1998 Q3 10-Feb-1998 Q3 16-Feb-1998 Q3 09-Feb-1998 Q3 09-Feb-1998 Q3 09-Feb-1998 Q3 06-Feb-1998 Q3 06-Feb-1998 Q3 06-Feb-1998 Q3 05-Feb-1998 Q3 04-Feb-1998 Q3 04-Feb-1998 Q3 04-Feb-1998 Q3 10-Feb-1998 Q3 20-Feb-1998 Q3 07-Jan-1998 Q3 27-Feb-1998 Q3 27-Feb-1998 Q3 27-Feb-1998 Q3 26-Feb-1998 Q3 26-Feb-1998 Q3 25-Feb-1998 Q3 23-Feb-1998 Q3 23-Feb-1998 Q3 23-Feb-1998 Q3 13-Feb-1998 Q3 20-Feb-1998 Q3 16-Feb-1998 Q3 19-Feb-1998 Q3 19-Feb-1998 Q3 19-Feb-1998 Q3 18-Feb-1998 Q3 18-Feb-1998 Q3 18-Feb-1998 Q3 18-Feb-1998 Q3 17-Feb-1998 Q3 17-Feb-1998 Q3 20-Feb-1998 Q3 20-Feb-1998 Q3 14-Jan-1998 Q3 21-Jan-1998 Q3 20-Jan-1998 Q3 19-Jan-1998 Q3 19-Jan-1998 Q3 19-Jan-1998 Q3 16-Jan-1998 Q3 16-Jan-1998 Q3 15-Jan-1998 Q3 14-Jan-1998 Q3 14-Jan-1998 Q3 21-Jan-1998 Q3 14-Jan-1998 Q3 13-Jan-1998 Q3 13-Jan-1998 Q3 13-Jan-1998 Q3 12-Jan-1998 Q3 09-Jan-1998 Q3 09-Jan-1998 Q3 09-Jan-1998 Q3 08-Jan-1998 Q3 03-Feb-1998 Q3 23-Feb-1998 Q3 08-Jan-1998 Q3 14-Jan-1998 Q3 30-Jan-1998 Q3 02-Feb-1998 Q3 02-Feb-1998 Q3 12-Jan-1998 Q3 21-Jan-1998 Q3 03-Feb-1998 Q3 02-Feb-1998 Q3 30-Jan-1998 Q3 30-Jan-1998 Q3 30-Jan-1998 Q3 30-Jan-1998 Q3 29-Jan-1998 Q3 22-Jan-1998 Q3 23-Jan-1998 Q3 30-Jan-1998 Q3 29-Jan-1998 Q3 23-Jan-1998 Q3 23-Jan-1998 Q3 23-Jan-1998 Q3 23-Jan-1998 Q3 23-Jan-1998 Q3 21-Jan-1998 Q3 26-Jan-1998 Q3 26-Jan-1998 Q3 29-Jan-1998 Q3 14-Apr-1998 Q4 15-Apr-1998 Q4 15-Apr-1998 Q4 13-Apr-1998 Q4 16-Apr-1998 Q4 13-Apr-1998 Q4 14-Apr-1998 Q4 16-Apr-1998 Q4 16-Apr-1998 Q4 17-Apr-1998 Q4 17-Apr-1998 Q4 20-Apr-1998 Q4 13-Apr-1998 Q4 08-Apr-1998 Q4 20-Apr-1998 Q4 20-Apr-1998 Q4 20-Apr-1998 Q4 20-Apr-1998 Q4 17-Apr-1998 Q4 08-Apr-1998 Q4 07-Apr-1998 Q4 07-Apr-1998 Q4 01-May-1998 Q4 21-Apr-1998 Q4 07-Apr-1998 Q4 08-Apr-1998 Q4 08-Apr-1998 Q4 10-Apr-1998 Q4 08-Apr-1998 Q4 13-Apr-1998 Q4 09-Apr-1998 Q4 10-Apr-1998 Q4 10-Apr-1998 Q4 10-Apr-1998 Q4 10-Apr-1998 Q4 10-Apr-1998 Q4 10-Apr-1998 Q4 10-Apr-1998 Q4 08-Apr-1998 Q4 04-May-1998 Q4 30-Apr-1998 Q4 01-May-1998 Q4 01-May-1998 Q4 01-May-1998 Q4 01-May-1998 Q4 01-May-1998 Q4 04-May-1998 Q4 30-Apr-1998 Q4 04-May-1998 Q4 28-Apr-1998 Q4 04-May-1998 Q4 05-May-1998 Q4 05-May-1998 Q4 06-May-1998 Q4 06-May-1998 Q4 06-Apr-1998 Q4 13-Mar-1998 Q4 04-May-1998 Q4 24-Apr-1998 Q4 21-Apr-1998 Q4 22-Apr-1998 Q4 22-Apr-1998 Q4 23-Apr-1998 Q4 23-Apr-1998 Q4 23-Apr-1998 Q4 24-Apr-1998 Q4 29-Apr-1998 Q4 24-Apr-1998 Q4 29-Apr-1998 Q4 24-Apr-1998 Q4 24-Apr-1998 Q4 27-Apr-1998 Q4 27-Apr-1998 Q4 27-Apr-1998 Q4 27-Apr-1998 Q4 28-Apr-1998 Q4 21-Apr-1998 Q4 24-Apr-1998 Q4 13-Mar-1998 Q4 11-Mar-1998 Q4 11-Mar-1998 Q4 11-Mar-1998 Q4 12-Mar-1998 Q4 12-Mar-1998 Q4 13-Mar-1998 Q4 13-Mar-1998 Q4 16-Mar-1998 Q4 06-May-1998 Q4 09-Mar-1998 Q4 16-Mar-1998 Q4 16-Mar-1998 Q4 17-Mar-1998 Q4 18-Mar-1998 Q4 18-Mar-1998 Q4 18-Mar-1998 Q4 18-Mar-1998 Q4 13-Mar-1998 Q4 04-Mar-1998 Q4 02-Mar-1998 Q4 02-Mar-1998 Q4 02-Mar-1998 Q4 03-Mar-1998 Q4 03-Mar-1998 Q4 03-Mar-1998 Q4 04-Mar-1998 Q4 10-Mar-1998 Q4 04-Mar-1998 Q4 09-Mar-1998 Q4 04-Mar-1998 Q4 05-Mar-1998 Q4 05-Mar-1998 Q4 06-Mar-1998 Q4 06-Mar-1998 Q4 06-Mar-1998 Q4 09-Mar-1998 Q4 18-Mar-1998 Q4 04-Mar-1998 Q4 02-Apr-1998 Q4 18-Mar-1998 Q4 27-Mar-1998 Q4 27-Mar-1998 Q4 30-Mar-1998 Q4 30-Mar-1998 Q4 30-Mar-1998 Q4 31-Mar-1998 Q4 27-Mar-1998 Q4 02-Apr-1998 Q4 26-Mar-1998 Q4 02-Apr-1998 Q4 02-Apr-1998 Q4 02-Apr-1998 Q4 03-Apr-1998 Q4 03-Apr-1998 Q4 03-Apr-1998 Q4 03-Apr-1998 Q4 06-Apr-1998 Q4 01-Apr-1998 Q4 23-Mar-1998 Q4 06-Apr-1998 Q4 19-Mar-1998 Q4 19-Mar-1998 Q4 19-Mar-1998 Q4 19-Mar-1998 Q4 20-Mar-1998 Q4 20-Mar-1998 Q4 27-Mar-1998 Q4 20-Mar-1998 Q4 18-Mar-1998 Q4 23-Mar-1998 Q4 23-Mar-1998 Q4 23-Mar-1998 Q4 24-Mar-1998 Q4 24-Mar-1998 Q4 24-Mar-1998 Q4 25-Mar-1998 Q4 26-Mar-1998 Q4 20-Mar-1998 Q4
- Query with Calculated Field:
-
So, I think I understand this to the most part, but I see an issue or two. First, Where to I keep the function? do I do this in a module? Also, This seems to be year specific. I would like to be able to use a criteria form for the report, will I have to keep changing dates or is there a way to do this for any year? What about the fact that july through dec 2008 are actually part of fiscal year 2009? it seems that this query would still list these months in the wrong year.
Thanks for all your help, you are very appriciatedComment
-
In a Standard Code ModuleWhere to I keep the function?
Previously answeredDo I do this in a module?
It can probably be converted to accept any YearI would like to be able to use a criteria form for the report, will I have to keep changing dates or is there a way to do this for any year?
Maybe I'm confused, (fiscally illerate), but isn't July 2008 considered to be Quarter 1 of Fiscal Year 2009, and isn't December 2008 considered to be Quarter 3 of Fiscal Year 2009?What about the fact that july through dec 2008 are actually part of fiscal year 2009? it seems that this query would still list these months in the wrong year.
P.S. - Kindly Reply with specifics.Comment
-
Hi. ADezii has shown a query which can with a few adjustments be generalised to work with any year to return a quarter value. It appears to me that what you are really asking for is slightly different - a way to return a year and quarter reference, as you mention being able to show the fiscal year as part of the value.
The function below is an adaptation of one I use to return fiscal year quarters. Like the one ADezii gave you this has to be located in any public code module - one that is shown in the Modules tab within Access. If there are none at present just create a new one and paste the code into the module then save it under any suitable name (not the name of the function, though!).
In the query editor you can use the function to return the quarter reference given the transaction date, as a calculated field set along the lines of
FiscalQuarter: fYearQuarter([your transaction date])
The function returns the quarter year-first to ensure that results can be ordered in ascending or descending order correctly.
Test data (uk date format - dd/mm/yyyy)Code:Public Function fYearQuarter(somedate, Optional quarter_start = 3) As String 'Returns the current financial year quarter for the current financial year 'in the form yyyy-Qn where yyyy is the financial year and n is the 'quarter number. 'Assumes that the financial year starts in quarter 3 of the calendar year 'which can be varied by changing the default quarter_start value Dim intQ As Integer, intYear As Integer If Not IsNull(somedate) Then intYear = Year(somedate) Select Case quarter_start Case 1 To 4 intQ = CLng(Format(somedate, "q")) - quarter_start + 1 If intQ <= 0 Then intQ = intQ + 4 intYear = intYear - 1 End If fYearQuarter = intYear & "-Q" & intQ Case Else fYearQuarter = "invalid start quarter" End Select End If End Function
-StewartCode:Date Quarter 01/07/2008 2008-Q1 01/08/2008 2008-Q1 01/09/2008 2008-Q1 01/10/2008 2008-Q2 01/11/2008 2008-Q2 01/12/2008 2008-Q2 01/01/2009 2008-Q3 01/02/2009 2008-Q3 01/03/2009 2008-Q3 01/04/2009 2008-Q4 01/05/2009 2008-Q4 01/06/2009 2008-Q4 01/07/2009 2009-Q1 01/08/2009 2009-Q1 01/09/2009 2009-Q1 01/10/2009 2009-Q2 01/11/2009 2009-Q2 01/12/2009 2009-Q2 01/01/2010 2009-Q3 01/02/2010 2009-Q3 01/03/2010 2009-Q3 01/04/2010 2009-Q4 01/05/2010 2009-Q4 01/06/2010 2009-Q4 01/07/2010 2010-Q1 01/08/2010 2010-Q1 01/09/2010 2010-Q1 01/10/2010 2010-Q2 01/11/2010 2010-Q2 01/12/2010 2010-Q2
Comment
-
By the way, I have used a slightly different convention for the year than you mention - in the UK financial years are usually quoted from the year they start, not the year they end, so 1 July 2008 would be Quarter 1 of fiscal year 2008/2009, hence the 2008 you see in the table above.
If you need to have this listed as 2009 the same function can be used - it is just a slight change that is needed, to line 9:
intYear = Year(somedate) + 1
and the test data then becomes
-StewartCode:Date Quarter 01/07/2008 2009-Q1 01/08/2008 2009-Q1 01/09/2008 2009-Q1 01/10/2008 2009-Q2 01/11/2008 2009-Q2 01/12/2008 2009-Q2 01/01/2009 2009-Q3 01/02/2009 2009-Q3 01/03/2009 2009-Q3 01/04/2009 2009-Q4 01/05/2009 2009-Q4 01/06/2009 2009-Q4 01/07/2009 2010-Q1 01/08/2009 2010-Q1 01/09/2009 2010-Q1 01/10/2009 2010-Q2 01/11/2009 2010-Q2 01/12/2009 2010-Q2 01/01/2010 2010-Q3 01/02/2010 2010-Q3 01/03/2010 2010-Q3 01/04/2010 2010-Q4 01/05/2010 2010-Q4 01/06/2010 2010-Q4 01/07/2010 2011-Q1 01/08/2010 2011-Q1 01/09/2010 2011-Q1 01/10/2010 2011-Q2 01/11/2010 2011-Q2 01/12/2010 2011-Q2
Comment
-
Hello Stewart, always a pleasure to see you. I'm really confused over this whole 'Fiscal Year' definition. If you have time, can you kindly define the 4 Quarters for Fiscal Year 2009, as in:
Thanks Stewart!Code:Quarter 1 [2009] - 6/1/2008 to 9/30/2008, etc.
Comment
-
Hi ADezii. In the financial community the fiscal year defines a specific tax period for accounting purposes. In the UK the norm for businesses is to have a financial year which runs from 1 April in one year to 31 March in the next year - but there are other patterns similar to the one mentioned by the original poster in post # 1. For example, as a matter of practicality Further Education colleges in Scotland changed their financial year patterns from 1 April year-start to 1 August to do away with the difficulty up to then of matching student activity running from August that straddled two financial years.
As today is 1 April, for many businesses in the UK financial year 2009/10 has just begun (or fiscal year 2010 in terms of the posts above). April, May and June 2009 form quarter 1 of 2009/10, July, August and September 2009 quarter 2, October, November and December 2009 quarter 3, and January, February and March 2010 form quarter 4 of 2009/10. The financial year/fiscal year is the accounting period for which businesses must by law publish audited accounts (at least in the UK). That period (whether referred to as 2009/10 or fiscal year 2010 or whatever else may be conventional) does not change just because the calendar year changes - so January, February and March of 2010 are simply referred to as quarter 4 of 2009/10.
In post 1 the poster refers to a fiscal year starting in quarter 3 (1 July each year), so the first half of the calendar year will belong to the previous fiscal year and the second the next, which is why in the exemplars listed in my final post you see the fiscal year part change in July. The fiscal year quarters for the poster's next fiscal year (2010 if I've correctly picked up the convention) run as follows:
2010 quarter 1: 07/01/2009 - 09/30/2009
2010 quarter 2: 10/01/2009 - 12/31/2009
2010 quarter 3: 01/01/2010 - 03/31/2010
2010 quarter 4: 04/01/2010 - 06/30/2010
Hope my imperfect explanation helps a little.
Regards
StewartComment
-
I hope I haven't oversimplified, but wouldn't the following work for you? :
Replace Date() with any date field you need to work with, and quotes (') for double-quotes (") when used within SQL to be technically correct.Code:Format(DateAdd("m",7,Date()),"\qq yyyy")Comment
-
I certainly hope so ADezii :D
But in truth, I think RCollins has the right idea. All ideas are worth exploring.Comment
-
Not quite working, NeoPa (one quarter out in the changeovers), but it's a nice simplification. Applied to the same testdata as above:
-StewartCode:Date Quarter Test 01/07/2008 2009-Q1 q1 2009 01/08/2008 2009-Q1 q1 2009 01/09/2008 2009-Q1 q2 2009 01/10/2008 2009-Q2 q2 2009 01/11/2008 2009-Q2 q2 2009 01/12/2008 2009-Q2 q3 2009 01/01/2009 2009-Q3 q3 2009 01/02/2009 2009-Q3 q3 2009 01/03/2009 2009-Q3 q4 2009 01/04/2009 2009-Q4 q4 2009 01/05/2009 2009-Q4 q4 2009 01/06/2009 2009-Q4 q1 2010 01/07/2009 2010-Q1 q1 2010 01/08/2009 2010-Q1 q1 2010 01/09/2009 2010-Q1 q2 2010 01/10/2009 2010-Q2 q2 2010 01/11/2009 2010-Q2 q2 2010 01/12/2009 2010-Q2 q3 2010 01/01/2010 2010-Q3 q3 2010 01/02/2010 2010-Q3 q3 2010 01/03/2010 2010-Q3 q4 2010 01/04/2010 2010-Q4 q4 2010 01/05/2010 2010-Q4 q4 2010 01/06/2010 2010-Q4 q1 2011 01/07/2010 2011-Q1 q1 2011 01/08/2010 2011-Q1 q1 2011 01/09/2010 2011-Q1 q2 2011 01/10/2010 2011-Q2 q2 2011 01/11/2010 2011-Q2 q2 2011 01/12/2010 2011-Q2 q3 2011
Comment
-
...but with the following slight tweak
it gives...Code:Format(DateAdd("m", 6, [somedate]), "yyyy-\Qq")
-StewartCode:Date Quarter Test 01/07/2008 2009-Q1 2009-Q1 01/08/2008 2009-Q1 2009-Q1 01/09/2008 2009-Q1 2009-Q1 01/10/2008 2009-Q2 2009-Q2 01/11/2008 2009-Q2 2009-Q2 01/12/2008 2009-Q2 2009-Q2 01/01/2009 2009-Q3 2009-Q3 01/02/2009 2009-Q3 2009-Q3 01/03/2009 2009-Q3 2009-Q3 01/04/2009 2009-Q4 2009-Q4 01/05/2009 2009-Q4 2009-Q4 01/06/2009 2009-Q4 2009-Q4 01/07/2009 2010-Q1 2010-Q1 01/08/2009 2010-Q1 2010-Q1 01/09/2009 2010-Q1 2010-Q1 01/10/2009 2010-Q2 2010-Q2 01/11/2009 2010-Q2 2010-Q2 01/12/2009 2010-Q2 2010-Q2 01/01/2010 2010-Q3 2010-Q3 01/02/2010 2010-Q3 2010-Q3 01/03/2010 2010-Q3 2010-Q3 01/04/2010 2010-Q4 2010-Q4 01/05/2010 2010-Q4 2010-Q4 01/06/2010 2010-Q4 2010-Q4 01/07/2010 2011-Q1 2011-Q1 01/08/2010 2011-Q1 2011-Q1 01/09/2010 2011-Q1 2011-Q1 01/10/2010 2011-Q2 2011-Q2 01/11/2010 2011-Q2 2011-Q2 01/12/2010 2011-Q2 2011-Q2
Comment
Comment