Calendar calculation (possible bug)

Katja

I am having difficulty with a calendar calculation that I use to calculate planned completion %. When dates have more than 30 days or when a non-working (holiday) is part of the month, the formula is not working as intended.

Below is the formula in the planned % complete column: =IF([Planned Finish]4 = TODAY(), 1, IF([Planned Finish]4 < TODAY(), 1, IF([Planned Start]4 > TODAY(), 0, IF([Planned Start]4 = TODAY(), ((TODAY() - [Planned Start]4) / Duration4), IF([Planned Start]4 < TODAY(), ((TODAY() - [Planned Start]4) / Duration4), IF([Planned Finish]4 > TODAY(), ((TODAY() - [Planned Start]4) / Duration4)))))))

This happens today (5/31): my planned start date is 5/25 and my planned Finish date is 6/1 the planned percent complete = 120

Could it be the additional holiday and 31st day of the month that is causing this formula to return incorrect results?

Shaine Greenwood

Hi, 

I think you're correct in that holidays are throwing your calculation off. Holidays and non-working days (Saturday and Sunday) aren't counted in the duration, so you've got a task that spans 8 days but only 5 of them are counted as working days. 

Katja

Shaine - do you have a recommendation for a work around?

Shaine Greenwood

One thing that you might try, although it'll make your formula larger, is replacing your duration cell reference with NETDAYS(start_date, end_date). I'm not exactly sure if this'll work for your needs but give it a shot.

Help Center content: https://help.smartsheet.com/function/netdays