Date calculation that updates the year automatically

Julian Sindall

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.

Bassam Khalil

Hi @Julian Sindall

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

and please add a sample case show what you need and what is the current result for your formula

Julian Sindall

Hi, a screenshot would just show a load of errors, and I fear that sharing the sheet would confuse more than assist, but let me try to explain further:

Let's assume I have a project worth £6,000 that will run between Oct 21 and Mar 22 i.e. £1,000/month.

In my pipeline I have the project value, start/end dates as above, and twelve columns: Month 1 (i.e. today's month), Month 2, Month 3,...Month 12. As we are in July now, Months 1-3 should show zero, then Months 4-9 should show £1000 each, then Months 10-12 show zero. Next month, the values should adjust so Months 1-2 and 9-12 show zero, but Months 3-8 show £1000 each. My formula works perfectly in January...but goes wrong after that.

Taking the above example as of July, it tries to calculate the number of working days in January (six months away) as NETWORKDAYS(MAX(DATE(YEAR(TODAY()), MONTH(TODAY()) + 6, 1), Start14), MIN(DATE(YEAR(TODAY()), MONTH(TODAY()) + 7, 1) - 1, Finish14)). It falls over because f it takes the month from today ie. July = 7, plus 6 months = 13 months = error. Unlike Excel, which works out that 13 months = +1 year and +1 month, Smartsheets does not. I can't find an automated workaround.

Bassam Khalil

@Julian Sindall

Please share an excel file exported for your sheet and delete any data just to help me to use the same name of your column to create the formula for you.