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