Problem Converting Fiscal Year to Calendar Dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1526976%22%20slang%3D%22en-US%22%3EProblem%20Converting%20Fiscal%20Year%20to%20Calendar%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1526976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20need%20help%20using%20a%20query%20to%20convert%20Fiscal%20year%20to%20Calendar%20Year.%20For%20Example%2C%20I%20have%20the%20Fiscal_Year%20column%20and%20Fiscal_Quarter%20Column%2C%20as%20shown%20below%3C%2FSPAN%3E%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EFiscal_Year%3C%2FTD%3E%3CTD%3EFiscal_Qtr%3C%2FTD%3E%3CTD%3ECalendar_Year%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY21%3C%2FTD%3E%3CTD%3E4Q%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY22%3C%2FTD%3E%3CTD%3E4Q%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY21%3C%2FTD%3E%3CTD%3E2Q%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY24%3C%2FTD%3E%3CTD%3E4Q%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY24%3C%2FTD%3E%3CTD%3E2Q%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CSPAN%3EI%20need%20help%20coming%20up%20with%20a%20query%20of%20converting%20the%20Fiscal%20year%20to%20normal%20calendar%20dates%20group%20of%20Month%2FDate%2FYear(mm%2Fdd%2Fyyyy).%20if%20it%20is%20Fiscal_Year%20FY21%20and%20Fiscal_Qtr%20is%204Q%2C%20then%20the%20date%20should%20be%209%2F30%2F2021%2C%20and%20if%20it%20is%20Fiscal_Year%20FY21%20and%20Fiscal_Qtr%20is%202Q%20then%20the%20Calendar_Year%20should%20be%2006%2F30%2F2021%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1526976%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531067%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20Converting%20Fiscal%20Year%20to%20Calendar%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531067%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F197606%22%20target%3D%22_blank%22%3E%40amasikonde%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAs%20a%20general%20rule%2C%20I%20prefer%20table-driven%20solutions%20for%20requirements%20like%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20simple%20%22tally%20table%22%20of%26nbsp%3B%20Fiscal%20Quarter%20and%20Fiscal%20Month%20(with%20last%20day%20of%20month)%20would%20be%20my%20choice%20for%20this%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI.e.%20Fiscal%20Quarter%204%20ends%20on%20September%2030th%2C%20Fiscal%20Quarter%201%20ends%20on%20March%2031st.%20So%2C%20if%20you%20are%20looking%20for%20the%20ending%20date%20of%20the%204th%20Fiscal%20Quarter%20of%202022%2C%20you%20can%20join%20that%20table%20to%20the%20other%20table(s)%20you%20need%20to%20work%20with%20and%20use%20it%20to%20calculate%20the%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20tasks%20like%20this%20can%20be%20accomplished%20with%20VBA%20functions%2C%20I%20just%20think%20it's%20better%20to%20go%20with%20the%20simplest%20solution%20that%20works.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533079%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20Converting%20Fiscal%20Year%20to%20Calendar%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533079%22%20slang%3D%22en-US%22%3E%3CP%3Ehow%20do%20I%20combine%20these%20in%20one%20column%20in%20access%20%3CSTRONG%3E%3CEM%3EIIF(%5BSales%20Projection%20Year)%3DFY21%2C%202021%3C%2FEM%3E%3C%2FSTRONG%3Eor%26nbsp%3BI%3CEM%3E%3CSTRONG%3EIF(%5BSales%20Projection%20Year)%3DFY22%2C%202022%3C%2FSTRONG%3E%3C%2FEM%3E%20or%26nbsp%3B%3CSTRONG%3E%3CEM%3EIIF(%5BSales%20Projection%20Year)%3DFY23%2C%202023%3C%2FEM%3E%3C%2FSTRONG%3E.%20Basically%20I%20just%20want%20this%20equation%20to%20return%20a%20full%20year%20instead%20of%20Fiscal%20year.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F126074%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533128%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20Converting%20Fiscal%20Year%20to%20Calendar%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533128%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F197606%22%20target%3D%22_blank%22%3E%40amasikonde%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%2C%20again%2C%20use%20a%20Tally%20Table%20that%20maps%20Fiscal%20Years%20and%20Quarters%20to%20Calendar%20Dates.%20Then%20I'd%20join%20that%20to%20the%20base%20table%20INSTEAD%20of%20that%20Conditional%20If.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can%20provide%20a%20spreadsheet%20of%20sample%20data--real%20fields%20as%20they%20appear%20in%20the%20actual%20table--I%20can%20put%20together%20an%20example%20in%20Access.%20It's%20really%20hard%20to%20spin%20up%20samples%20from%20partial%20descriptions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1533507%22%20slang%3D%22en-US%22%3ERe%3A%20Problem%20Converting%20Fiscal%20Year%20to%20Calendar%20Dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1533507%22%20slang%3D%22en-US%22%3E%3CP%3EGeorge%2C%20I%20was%20actually%20able%20to%20write%20equations%20to%20return%20the%20respective%20columns.%20for%20FY%20i%20wrote%20Planned_Year_Sales%3Aiif(%5BPlanned%20Sales%20Year%5D%3D%22FY21%22%2C2021%2C(%5BPlanned%20Sales%20Year%5D%3D%22FY22%22%2C2022%2C(%5BPlanned%20Sales%20Year%5D%3D%22FY23%22%2C2023)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20for%20the%20Quarter%20I%20wrote%20Planned_Month_Sales%3AIIF(%5BPlanned%20Sales%20Quarter%5D%3DQ%2C12%2CIIF(%5BPlanned%20Sales%20Quarter%5D%3DQ2%2C3%2CIIF(%5BPlanned%20Sales%20Quarter%5D%3DQ3%2C6IIF(%5BPlanned%20Sales%20Quarter%5D%3DQ4%2C9))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20for%20the%20date%20I%20picked%20the%20last%20date%20of%20the%20month%20of%20the%20Planned_Month_Sales%20and%20I%20wrote%20Planned_Date_Sales%3AIIF(%5BPlanned_Month_Sale%5D%3D12%2C1%2CIIF(%5BPlanned_Month_Sale%5D%3D3%2C31%2CIIF(%5BPlanned_Month_Sale%5D%3D3%2C6%2CIIF(%5BPlanned_Month_Sale%5D%3D4%2C9))))%3C%2FP%3E%3CTABLE%20width%3D%22820%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22125%22%3EPlanned%20Sales%20Year%3C%2FTD%3E%3CTD%20width%3D%22146%22%3EPlanned%20Sales%20Quarter%3C%2FTD%3E%3CTD%20width%3D%22133%22%3EPlanned_Year_Sales%3C%2FTD%3E%3CTD%20width%3D%22147%22%3EPlanned_Month_Sales%3C%2FTD%3E%3CTD%20width%3D%22135%22%3EPlanned_Date_Sales%3C%2FTD%3E%3CTD%20width%3D%22134%22%3EActual%20Planned%20Date%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY21%3C%2FTD%3E%3CTD%3EQ%3C%2FTD%3E%3CTD%3E2021%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E31%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY21%3C%2FTD%3E%3CTD%3E2Q%3C%2FTD%3E%3CTD%3E2021%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E31%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY21%3C%2FTD%3E%3CTD%3E3Q%3C%2FTD%3E%3CTD%3E2021%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY21%3C%2FTD%3E%3CTD%3E4Q%3C%2FTD%3E%3CTD%3E2021%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY22%3C%2FTD%3E%3CTD%3EQ%3C%2FTD%3E%3CTD%3E2022%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E31%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY22%3C%2FTD%3E%3CTD%3E2Q%3C%2FTD%3E%3CTD%3E2022%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E31%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY22%3C%2FTD%3E%3CTD%3E3Q%3C%2FTD%3E%3CTD%3E2022%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY22%3C%2FTD%3E%3CTD%3E4Q%3C%2FTD%3E%3CTD%3E2022%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY23%3C%2FTD%3E%3CTD%3EQ%3C%2FTD%3E%3CTD%3E2023%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E31%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY23%3C%2FTD%3E%3CTD%3E2Q%3C%2FTD%3E%3CTD%3E2023%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E31%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY23%3C%2FTD%3E%3CTD%3E3Q%3C%2FTD%3E%3CTD%3E2023%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFY23%3C%2FTD%3E%3CTD%3E4Q%3C%2FTD%3E%3CTD%3E2023%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E30%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3ENow%20I'm%20struggling%20to%20combine%20the%20numbers%20into%20a%20date%20in%20%22Actual%20Planned%20Date%22%20column%20to%20come%20up%20with%20example%202021%2F12%2F31%2C%20I%20would%20love%20and%20appreciate%20if%20you%20can%20help%20me%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F126074%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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_YearFiscal_QtrCalendar_Year
FY214Q 
FY224Q 
FY212Q 
FY244Q 
FY242Q 

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

@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

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

@amasikonde 

 

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

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 YearPlanned Sales QuarterPlanned_Year_SalesPlanned_Month_SalesPlanned_Date_SalesActual Planned Date
FY21Q20211231 
FY212Q2021331 
FY213Q2021630 
FY214Q2021930 
FY22Q20221231 
FY222Q2022331 
FY223Q2022630 
FY224Q2022930 
FY23Q20231231 
FY232Q2023331 
FY233Q2023630 
FY234Q2023930 

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