# Problem Converting Fiscal Year to Calendar Dates

Highlighted
Occasional Contributor

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

# Re: Problem Converting Fiscal Year to Calendar Dates

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

Highlighted

# Re: Problem Converting Fiscal Year to Calendar Dates

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

Highlighted

# Re: Problem Converting Fiscal Year to Calendar Dates

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.

Highlighted

# Re: Problem Converting Fiscal Year to Calendar Dates

George, I was actually able to write equations to return the respective columns. for FY i wrote Planned_Year_Sales:iif([Planned Sales Year]="FY21",2021,([Planned Sales Year]="FY22",2022,([Planned Sales Year]="FY23",2023)))

and for the Quarter I wrote Planned_Month_Sales:IIF([Planned Sales Quarter]=Q,12,IIF([Planned Sales Quarter]=Q2,3,IIF([Planned Sales Quarter]=Q3,6IIF([Planned Sales Quarter]=Q4,9))))

and for the date I picked the last date of the month of the Planned_Month_Sales and I wrote Planned_Date_Sales:IIF([Planned_Month_Sale]=12,1,IIF([Planned_Month_Sale]=3,31,IIF([Planned_Month_Sale]=3,6,IIF([Planned_Month_Sale]=4,9))))

 Planned Sales Year Planned Sales Quarter Planned_Year_Sales Planned_Month_Sales Planned_Date_Sales Actual Planned Date FY21 Q 2021 12 31 FY21 2Q 2021 3 31 FY21 3Q 2021 6 30 FY21 4Q 2021 9 30 FY22 Q 2022 12 31 FY22 2Q 2022 3 31 FY22 3Q 2022 6 30 FY22 4Q 2022 9 30 FY23 Q 2023 12 31 FY23 2Q 2023 3 31 FY23 3Q 2023 6 30 FY23 4Q 2023 9 30

Now I'm struggling to combine the numbers into a date in "Actual Planned Date" column to come up with example 2021/12/31, I would love and appreciate if you can help me @George Hepworth