Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Calendar calculation (possible bug)

Options
edited 12/09/19

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?

Tags:

• Employee
Options

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.

• Options

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

• Employee
Options

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

This discussion has been closed.