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