FR date format for rolling 12 month income statement

Question

There must be a simple formula for the FR date function on 12 month rolling income statements that crosses fiscal years. If I have the current selected period =@FR("End") in the first column and want the 11 preceding months in the next 11 columns, what is the formula for the dates?

wheumann · Accepted Answer

You can do this with some fairly simple formulas. 
 Start with FR formula =FR("End") to get the date for the current period end. 
 Use Excel formulas as shown below to get previous month end, unless you have a 4/4/5 calendar where you'd need the formula to subtract 28 vs 35 days. 
 Use NETP or BALP for the data in the column pertaining to the current period, and NET#PA or BAL#PA for the others, where "PA" means Periods Ago.

Column G 
 Column H 
 Column I 
 Column J

Formula Row 8 
 =FR("End") 
 =G8-day(G8) 
 =H8-day(H8) 
 =i8-day(i8)

Result 
 5/31/2022 
 4/30/2022 
 3/31/2022 
 2/28/2022

Formula on \ line 
 =FRAMTA("NETP") 
 =FRAMTA("NET1PA") 
 =FRAMTA("NET2PA") 
 =FRAMTA("NET3PA")

Django · Answer

Is your total column just a sum of =FRAMTA("NET1P")+FRAMTA("NET2P")+FRAMTA("NET3P")?

Sage 300

FR date format for rolling 12 month income statement

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING