Build a Burn Rate Formula

RKLRpp

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...

Jon Mark H

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

RKLRpp

Beautiful, that fixed it!

Thank you,

Rachel

Jon Mark H

You're very welcome!

Glad I could help!