Build a Burn Rate Formula
![RKLRpp](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Here is a puzzle I'm struggling with, would like to make a burnrate that distributes the total labor expected across the task period of performance. It does this by dividing the labor cost ($), by duration (work days), then multiplying that by the rate by the number of work days scheduled for the task in a month period. I have 12 columns for months...I have tried iterations of this formula (this one if for July):
=IF(AND([Task Start]@row >= DATE(2022, 7, 1), [Task Start]@row < DATE(2022, 7, 31)), (([Labor Roll-up]@row / Duration@row) * NETWORKDAYS([Task Start]@row, DATE(2022, 2, 31))), IF(AND([Task Start]@row < DATE(2022, 7, 1), [Task Finish]@row > DATE(2022, 7, 31)), (([Labor Roll-up]@row / Duration@row) * NETWORKDAYS(DATE(2022, 7, 1), DATE(2022, 7, 31))), IF(AND([Task Finish]@row > DATE(2022, 7, 1), [Task Finish]@row < DATE(2022, 7, 31))), ([Labor Roll-up]@row / Duration@row * (NETWORKDAYS(DATE(2022, 7, 1), [Task Finish]@row))), "")),
I get #INCORRECT ARGUMENT SET error. I'm not sure why...
Answers
-
Hey RKLRpp,
It looks like you closed your third IF statement prematurely - delete this parenthesis, and add one to the very end:
which should make it:
=IF(AND([Task Start]@row >= DATE(2022, 7, 1), [Task Start]@row < DATE(2022, 7, 31)), (([Labor Roll-up]@row / Duration@row) * NETWORKDAYS([Task Start]@row, DATE(2022, 2, 31))), IF(AND([Task Start]@row < DATE(2022, 7, 1), [Task Finish]@row > DATE(2022, 7, 31)), (([Labor Roll-up]@row / Duration@row) * NETWORKDAYS(DATE(2022, 7, 1), DATE(2022, 7, 31))), IF(AND([Task Finish]@row > DATE(2022, 7, 1), [Task Finish]@row < DATE(2022, 7, 31)), ([Labor Roll-up]@row / Duration@row * (NETWORKDAYS(DATE(2022, 7, 1), [Task Finish]@row))), "")))
If that doesn't work, could you share a screenshot (minus sensitive data) that would help understand this issue - or even with some dummy data?
-Jon Mark
-
Beautiful, that fixed it!
Thank you,
Rachel
-
You're very welcome!
Glad I could help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!