Build a Burn Rate Formula
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
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!