Convert Fiscal Year and Quarter to a Date in a Microsoft Access Query
Question details
Convert fiscal-year and quarter labels, such as FY21 and 4Q, into calendar dates using the organization's fiscal-calendar rules.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- An Access query needs to return a calendar date for each fiscal-year and quarter combination, such as 09/30/2021 for FY21 and 4Q.
- Observed behavior
- The desired result depends on the fiscal-year start month, quarter numbering, whether the fiscal-year label identifies the starting or ending year, and whether the output represents the quarter's first or last day.
Confirm the fiscal-year start month, how quarters are numbered, and whether the fiscal-year label refers to the starting or ending year. Also check the actual field names and label formats, and decide whether the query should return quarter-start or quarter-end dates.
Calculate quarter-end dates for an October–September fiscal year in Access
Use this query expression when Q1 is October–December, Q4 is July–September, and FY21 identifies the fiscal year ending in 2021.
For the United States federal fiscal calendar, FY21 runs from October 1, 2020, through September 30, 2021. Its quarter-end dates are December 31, 2020; March 31, 2021; June 30, 2021; and September 30, 2021.
DateSerial accepts month values beyond 12 and normalizes them into the appropriate year. Setting its day argument to 0 returns the last day of the preceding month.
Check your organization's fiscal-calendar documentation before editing the query. Use this expression only if Q1 covers October–December, Q2 January–March, Q3 April–June, and Q4 July–September, with the FY label naming the ending year.
In the Access Navigation Pane, right-click the query and choose Design View. Identify the fields containing labels such as FY21 and 4Q; the following example uses FiscalYear and FiscalQuarter.
In an empty Field cell in the query grid, enter: QuarterEnd: DateSerial(2000 + Val(Mid([FiscalYear],3)) - 1, 10 + 3 * Val(Left([FiscalQuarter],1)), 0). Replace FiscalYear and FiscalQuarter with your actual field names.
On the Query Design tab, click Run. Verify that FY21 with 1Q returns December 31, 2020, and FY21 with 4Q returns September 30, 2021. Investigate blank or malformed labels before relying on the calculated results.
Return to Design View, select the QuarterEnd calculated column, and open its Property Sheet. Set Format to mm/dd/yyyy to display September 30, 2021, as 09/30/2021 while retaining a date value.
Review fiscal-quarter dates in WPS Spreadsheets
WPS Office is a free, lightweight, easy-to-use alternative for spreadsheet-based fiscal-calendar work. Export your Access query results to an Excel workbook, then review them in WPS Spreadsheets with a familiar interface and Excel format compatibility for a smooth transition. WPS does not replace Access or execute Access database queries.
Frequently Asked Questions
Why does FY21 and 4Q correspond to September 30, 2021?
It does under an October–September fiscal calendar where FY21 names the ending year. Q4 covers July–September 2021, so its last day is September 30, 2021. Confirm those rules with your organization before adding the DateSerial expression in Access Query Design.
How do I return the first day of the fiscal quarter instead?
For the same October–September calendar and ending-year labels, enter this in an empty Field cell in Query Design: QuarterStart: DateSerial(2000 + Val(Mid([FiscalYear],3)) - 1, 10 + 3 * (Val(Left([FiscalQuarter],1)) - 1), 1). FY21 and 4Q will return July 1, 2021.
What changes if FY21 means the fiscal year starting in 2021?
For an October start, remove the - 1 from the year argument in the calculated field. The quarter-end expression becomes DateSerial(2000 + Val(Mid([FiscalYear],3)), 10 + 3 * Val(Left([FiscalQuarter],1)), 0). Under that convention, FY21 and 4Q return September 30, 2022.
Can I use the expression with quarter labels such as Q4?
Not unchanged: Left([FiscalQuarter],1) expects the first character to be the quarter number. For labels exactly like Q4, replace that portion with Val(Right([FiscalQuarter],1)) in the Query Design calculated field. Validate that quarter values are 1–4, because DateSerial can normalize out-of-range months rather than flagging incorrect quarter data.
When is a date lookup table better than a calculated expression?
Use a lookup table when fiscal calendars vary, quarter dates have exceptions, or multiple queries need the same mapping. Create a table with FiscalYear, FiscalQuarter, and QuarterEndDate, ensuring each year-quarter pair is unique. In Query Design, add that table, join both label fields to the source fields, and include QuarterEndDate in the output.
