I'm working on a sheet to track our new puppy's bathroom habits, and among other features it takes in date, time, and true/false flags for pee and poop. I am trying to figure out how to make a column that shows the time between bowel movements of a specific type. (Warning in advance, this question is going to have a lot of pee and poop in it.)
I have successfully figured out how to log the time between two unspecified trips to the bathroom using simple cell subtraction i.e. =(B9-B8)+(A9-A8) (time strings subtracted, plus date strings subtracted to prevent negative values across midnight). However, this only works for consecutive bowel movements of the same type. For example, let's assume I am trying to track time since last poop. The desired timestamps will be scattered between other pee entries, so I can't just request a certain number of cells back or the previous full cell.
The pseudo-code solution is IF(cell has a poop flag, FIND the previous cell WHERE poop flag is TRUE and calculate the difference between timestamps, ELSE leave blank). The part I am stuck on is getting the cell value for that previous cell.
So far, all of the searches I've tried online give results for finding the last populated cell, where last means "final" instead of "previous". I suspect there's an INDEX trick to read the previous true/false value, but I haven't figured it out yet. Can anyone point me in the right direction?
