# Difference between Today & 7 Days Ago

Options
✭✭✭✭
edited 07/19/22

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.

Tags:

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

And where exactly are you wanting to put the total?

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!