Fiscal calanders

I would like to find out if there is a way of getting the Eomonth formula to work with a fiscal calendar? For example currently if I use the EOMonth formula for any date in February, I will get 2/29/16. however cut off date for February is 2/21/16. I have set up an array that has the Fiscal cut off dates, I have activities that occur on various dates through out the month. I want to calculate the which activities fall in a particular month. Ideally any activity that is scheduled between 1/25/16 and 2/21/16 is in the February fiscal month, all other activities would fall in other fiscal months.

Is it a set number of days from the end of the month? If so, just subtract that number from the EOMONTH function.

Alternatively, use the VLOOKUP function on your array of cut-off dates.

Hope this helps.

Pete

I have created a chart with present and future dates. In this chart I use a vlookup formula to look up information from a pivot table. I have also added error handling so that if a date is not in the pivot table that is on the chart if leaves the cell blank. This works great for all future dates.

However when I have created a graph from the table the data lines on the graph go to zero. this make the chart look messy.

How can I get the graph to not drop to zero for future dates but stop once it reaches the current date. I only want the graph to read the cells with results to the formula, and ignore the cells that just contain a formula or the result of the formula that equals "".
I was hoping that this could be done automatically so I don't have to go to the select data window and turn on the next future date, or have to go to the chart and select the formula and drag it to the next new date. All I want to do is Refresh the pivot table and everything else updates automatically.

Those blanks are interpreted by Excel as zero. To avoid this, you can change your formula so that it returns #N/A instead of "" for future dates, as the error(s) will not be plotted on the graph. You can hide the errors in your table by using conditional formatting, or if you do some arithmetic on the values in the table you can derive the values for the graph in a second table, and use that as the source for your graph.

Hope this helps.

Pete

Thank you I will try that. It just seems counter productive to have spent all that time eliminating the #n/a error because it makes the table look too busy and messy.