Forum Discussion
Problem Converting Fiscal Year to Calendar Dates
I need help using a query to convert Fiscal year to Calendar Year. For Example, I have the Fiscal_Year column and Fiscal_Quarter Column, as shown below
| Fiscal_Year | Fiscal_Qtr | Calendar_Year |
| FY21 | 4Q | |
| FY22 | 4Q | |
| FY21 | 2Q | |
| FY24 | 4Q | |
| FY24 | 2Q |
I need help coming up with a query of converting the Fiscal year to normal calendar dates group of Month/Date/Year(mm/dd/yyyy). if it is Fiscal_Year FY21 and Fiscal_Qtr is 4Q, then the date should be 9/30/2021, and if it is Fiscal_Year FY21 and Fiscal_Qtr is 2Q then the Calendar_Year should be 06/30/2021
4 Replies
amasikonde
As a general rule, I prefer table-driven solutions for requirements like this.A simple "tally table" of Fiscal Quarter and Fiscal Month (with last day of month) would be my choice for this one.
I.e. Fiscal Quarter 4 ends on September 30th, Fiscal Quarter 1 ends on March 31st. So, if you are looking for the ending date of the 4th Fiscal Quarter of 2022, you can join that table to the other table(s) you need to work with and use it to calculate the date.
While tasks like this can be accomplished with VBA functions, I just think it's better to go with the simplest solution that works.
how do I combine these in one column in access IIF([Sales Projection Year)=FY21, 2021or IIF([Sales Projection Year)=FY22, 2022 or IIF([Sales Projection Year)=FY23, 2023. Basically I just want this equation to return a full year instead of Fiscal year.George_Hepworth
I would, again, use a Tally Table that maps Fiscal Years and Quarters to Calendar Dates. Then I'd join that to the base table INSTEAD of that Conditional If.
If you can provide a spreadsheet of sample data--real fields as they appear in the actual table--I can put together an example in Access. It's really hard to spin up samples from partial descriptions.
