Calculate date diff from previous row

Mark Hibyan

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

Paul Newcome

What error are you getting? This formula should be going into its own date type column.

Paul Newcome

How are new entries being made?

Mark Hibyan

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

Paul Newcome

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)), "")

Mark Hibyan

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?

Paul Newcome

What error are you getting? This formula should be going into its own date type column.

Mark Hibyan

Oh, silly me. OK, I'm all set now. Thanks @Paul Newcome !

Paul Newcome

Happy to help. 👍️