Programatically changing date ranges per column

Options

Dear all,

I created a capacity overview sheet that sums the hourly allocation of co-workers between two dates, i.e. between today() and today(7), so one week from now. I would now like to extend that functionality to between today(8) and today(14), today(15) and today(21), and so on.

Is there a smart way to do this programmatically?

Thanks in advance,

Max

Answers

  • Max Gulde
    Options

    For now, I solved it by adding the first row with increasing numerical values for each column WN, i.e.

    =SUMIFS({Hourly Allocation Range}, {Assigned To Range}, $[Primary Column]@row, {Start Range}, <=TODAY([W0]$1 * 7 + 1), {Finish Range}, >TODAY(([W0]$1 + 1) * 7))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!