Our income statement is show all 12 months. However, when we run the income statement as of 4/30/2018 we got values in May - December 2018. Is there a way to cut off the report as of 4/30/2018 (or based on the user input end of period)?

Thanks.

• Harry Lewis

Hello Gwendolin:

If you always want your Income Statement to show 12 months ending on a specified data, you could use a technique such as this:

When run, it would look like this:

• Gwendolin Liu

Hi Harry,

Thank you for the quick response. We actually are show from Jan - Dec. When running as of 4/30/2018, we hide future months from showing but there are values in those future months already which we don't want to included in the YTD calculations.  As you can see the screenshot below, I ran the data as of 4/30/2018 but May 2018 already has values in there.

Thanks.

• Harry Lewis

Hi Gwendolin -

Have you considered designing your report so that it only lists those months you want?

This example uses net change for each account:

Edited by Harry Lewis
• Gwendolin Liu

Hi Harry,

I thought about that but the way our finance department wants is the actual financial statement is linking to each department sheet (get the data using JET) and adding up the month columns. If the months are not there to begin with, then all the formulas are wrong.  As you can see the highlighted formula which is not a JET calculation. Not sure what's the best way to handle this. Thanks.

• Harry Lewis

Hello Gwendolin -

You could wrap your references in Excel IF() functions

Let's assume that we have (a) the last date we want in cell D5 and (b) the date for the month is in row 4:

=IF(G\$4<=\$D\$5,'WC Revenue & Losses'!G9,0)

• Gwendolin Liu

Hi Harry,

Thank you for your suggestion. I modified the Jet formula as below and it works:

=IF(O\$3>\$C\$6,0,IFERROR(GL("Cell",O67,,O\$3,O\$4,,\$D78,\$E78,\$F78,\$G78)*\$C78,0))