Formula to calculate difference between todays entry and the entry from 30 days ago

SAMLOG

Hi, I am trying to create a column that contains the difference from the number in the cell entered today and the number in the cell entered 30 days ago. The columns are dates.

i.e Column 7/1/22 Row 2 = 10, Column 6/1/22 Row 2 = 50. Difference Column Result: 40

And really, since "30 days ago" may not have been a business day and may not have a column, so I really need it to look at the 30th column (if that makes ANY sense).

Any ideas?!?

Jeff Reisman

@SAMLOG Let me see if I have this straight:

Your column names are dates? Are there any date values in the cells at all, and if not, can there be (like in Row 1 maybe?) Column names don't have any significance at all, you can't use a column name of 7/1/22 to calculate anything.

One way I handled this was Column names like "10 Days Ago", "9 Days Ago", etc., with formulas on the first row to grab the date of the day that was 10 business days ago, etc. With some helper columns (like one where automation populated the current date every work day) I could handle all these calculations.

SAMLOG

The columns all have dates with a value for each row/date. I want to have a column that calculates todays entry to 30 entries ago....I am sure I am not explaining this well!

SAMLOG

*I want to have a column that calculates the DIFFERENCE from todays entry to 30 entries ago....

marc4

Do you have access to somewhere that you can run the smartsheet api?

You want to take the latest column (today) and then find the column 30 columns away, then update the difference column to have the formula that is =today@row - 30days_ago@row

Simple with the api, not sure how you would do it directly in smartsheet.

/marc

SAMLOG

Unfortunately, I don't believe I do. I was wondering if I could create a column that pulls the row cell -30 from today then do the formula from that cell? But I haven't been able to figure that either. Thank you for taking a look though.

Mike TV

@SAMLOG

1. Create a checkbox column. Call it anything.
2. Create an automation that checks the box in that checkbox column when new rows are added and manually check it for all existing rows. So that all rows are always check marked at all times.
3. Create another automation that runs once per day and does a Copy Row to another sheet that you create and copy's any rows with that checked (so that it'll archive all rows each day).
4. Create an index match formula that pulls from that archive sheet what your value in that cell was 30 days ago. On the archive sheet you'll either need to create a system generated Created Date column or manually create a date column that you have an automation record a date into when a new row is added.