date and times calculating the difference  on two different rows
Answers

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?

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:
 Add an Auto Number type column called Auto
 Add a Text/Number type column called Row Number
 In one of the Row Number cells, enter the following formula which will find the current row's autonumber in the column of autonumbers 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)
 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]@row1)
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.
Help Article Resources
Categories
Check out the Formula Handbook template!