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

  • Jon Mark H
    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
    RKLRpp ✭✭

    Beautiful, that fixed it!

    Thank you,

    Rachel

  • Jon Mark H
    Jon Mark H ✭✭✭✭✭

    You're very welcome!

    Glad I could help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!