Difference between Today & 7 Days Ago
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.
Best Answer
-
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)
Answers
-
Will today's column always be the far left date column?
-
I can make today's column the first or the last. We do not have a preference.
-
And where exactly are you wanting to put the total?
-
Again, it doesn't really matter, I'm flexible to get a formula to work
-
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)
-
That worked perfectly. I'm still getting learning the Index function, so I appreciate the assist.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!