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)
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?
Comments
-
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.
-
Shaine - do you have a recommendation for a work around?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives