For my pipeline projection I'm trying to capture the forecast income from different projects (rows) for each month (column) over the next twelve months on a rolling basis i.e. from TODAY(). However, when the end of the year comes in range, the DATE formula does not change the year. Here's the formula in the column looking 5 months ahead:
=IF(AND(Finish14 > DATE(YEAR(TODAY()), MONTH(TODAY()) + 5, 1), Start14 <= DATE(YEAR(TODAY()), MONTH(TODAY()) + 6, 1) - 1), NETWORKDAYS(MAX(DATE(YEAR(TODAY()), MONTH(TODAY()) + 5, 1), Start14), MIN(DATE(YEAR(TODAY()), MONTH(TODAY()) + 6, 1) - 1, Finish14)) * Utilisation14 * [Assumed fee rate/£GBP/day exVAT]14, 0)
It uses the IF&AND formulae to filter out project fee that won't be earned in that month because the project starts after or finishes before the fifth month from now. Then the NETWORKDAYS works out how many working days are in the month between the first of the fifth month from today (or the start date), and the first of the sixth month from today minus one, i.e. the end of the fifth month (or the finish date). Finally it multiplies that answer by the utilisation rate and fee rate to give an approximate value in the month.
It all works fine until the fifth month from TODAY() is December (or January+), at which point the formula still thinks the YEAR() is the same as today's year so the calculation tries to calculate negative days.
I can update the year manually in relevant columns, or I can fix each column to a particular month in the year, but really, I just want to be able to look ahead 12 months on a rolling basis to work out what my income forecast looks like without having to fiddle each month. Any ideas welcome.
