Calculate date diff from previous row
Hello Smart People -
Simple question. I would like to keep a running tally of the time between dates in the same column as it progresses via rows, so [Date]@row - [Date]@PreviousRow. Make sense? I think in SQL we just use "Previous".
Thanks,
Mark
Best Answer
-
What error are you getting? This formula should be going into its own date type column.
Answers
-
How are new entries being made?
-
Hi Paul -
It's chronological. It's an "archive" sheet based off of a "live" sheet that is managing assets (the physical whereabouts of assets) via barcoding. Every time a barcode scan updates the live sheet (establishes a location), the row is copied to this archive sheet. On this archive sheet, I have a system "creation date" field to capture the date/time the asset moved, and a datepart field to parse out just the date (I only care about days). I just want to keep track of the times assets spend at each location.
Thanks,
Mark
-
Ok. I would suggest inserting an auto-number column (no special formatting). Then you can use something like this...
=IFERROR(Created@row - DATEONLY(INDEX(Created:Created, [Auto-Number Column]@row - 1)), "")
-
Hi Paul - I'm getting an error. In troubleshooting, it seems this method of retrieving values from previous rows using INDEX and [AutoNum]@row-1 doesn't work for date fields. I can isolate other data types like text, but not dates. Any ideas?
-
What error are you getting? This formula should be going into its own date type column.
-
Oh, silly me. OK, I'm all set now. Thanks @Paul Newcome !
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!