Forum Discussion
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.
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.
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