Calendar calculation (possible bug)

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?

Find more posts tagged with

Calendar View

Formulas

Comments

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