date and times calculating the difference - on two different rows

System

This discussion was created from comments split from: Calculate difference between two dates and times.

AmandaHore

Hi - This is a great formula. I am trying to get it working where the date and times were are calculating the difference on are on two different rows.

EG Row 1 has a Date and Time

Row 2 has a Date and time

Calculate the difference between the Date and Time in Row 2 versus row 1.

I have formula working in each row, just not sure how to adjust it to compare two different rows. Any suggestions?

Brian_Richardson

If you want to calculate between two different rows then you'll need a way to identify those rows.

One way is to add a couple of columns to get the Row Number onto the row data. Then you can do something like "calculate the difference between the date on the current row, and the row just above it".

To do that:

1. Add an Auto Number type column called Auto
2. Add a Text/Number type column called Row Number
3. In one of the Row Number cells, enter the following formula which will find the current row's auto-number in the column of auto-numbers and return it's position…which equates to the row number. Once you enter the formula, right click and choose Convert to Column Formula: =MATCH(Auto:Auto,Auto@row)
4. Now you can write your Date formula like this and set it to be a Column Formula (right click and choose Convert to Column Formula after you have entered it) = Date@row - INDEX(Date:Date, [Row Number]@row-1)

The INDEX function looks at the Date column and returns the date from the position that equals the current row number minus 1…the previous row.

You can adjust this based on how you want to do the lookup. If instead you're using a checkbox instead of a Row Number, then you can ignore the row number setup above and use a formula like this instead

= Date@row - INDEX (Date:Date, MATCH (1,Checkbox:Checkbox,0))

In this formula, the MATCH function finds the first checked checkbox (the "1" means true) in the Checkbox column. The 0 in MATCH means that it's searching the whole column in unordered fashion instead of alphabetically. Then the INDEX takes the row number that MATCH provides and returns the Date from that row. That's then subtracted from the Date on your current row.

You can vary the criteria to fit your needs in the MATCH statement.