Difference between Today & 7 Days Ago

Donni Matthew

We have a sheet that contains dates in the column headers with a # of widgets in the cells by office. We are wondering if there is a way to create a formula that would always compare the value for Today compared to the value from 7 days ago.

I've attached what our sheet looks like to help understand what we're trying to accomplish. As an example, in the ILD row we would like to show the difference between today (4/22/22 in our example) and & days prior (4/15/22). The result would be 5.

SmartsheetExample.jpg

Paul Newcome

I would suggest a text/number column to the left of the [Today] column. From there you would use

=INDEX(Today@row:[Last Date Column name]@row, 1, 2) - INDEX(Today@row:[Last Date Column name]@row, 1, 8)

Paul Newcome

Will today's column always be the far left date column?

Donni Matthew

I can make today's column the first or the last. We do not have a preference.

Paul Newcome

And where exactly are you wanting to put the total?

Donni Matthew

Again, it doesn't really matter, I'm flexible to get a formula to work

Paul Newcome

I would suggest a text/number column to the left of the [Today] column. From there you would use

=INDEX(Today@row:[Last Date Column name]@row, 1, 2) - INDEX(Today@row:[Last Date Column name]@row, 1, 8)

Donni Matthew

That worked perfectly. I'm still getting learning the Index function, so I appreciate the assist.

Paul Newcome

Happy to help. 👍️

The INDEX function does take a little getting used to but can open up a ton of possibilities.

=INDEX(range_to_pull_from, row_number, column_number)

In this instance we are pulling from a single row, so the row number is 1. Our column numbers start at 1 in the Today column, so the first date is going to be column 2 and the week prior will be column 8. The reason we use the Today column for our first column is because the formula will automatically include new columns added into the middle of the range. If you were to reference the [4/22/22] column as the leftmost column then insert a column to the left of that then you would have to go in and adjust the formula to account for the new column. Since it is "mid-range" is it automatically included and doesn't require the formula to be updated every time.