Compute difference between 2 date/time columns based on a condition

**kjsivy** · 10-09-2013, 09:36 PM

Hello,

I'm having trouble locating an answer to my specific question. I have 1 column of data with the date and time on which a biological survey was conducted over last winter, and in the second column I have the date and time of when each snowfall event during that winter ended. I need to calculate the number of hours elapsed since the last snowfall event for each biological survey conducted. The catch is that each "SurveyTime" record needs to be evaluated against each "SnowfallEnd" record to ensure that the time difference being computed is for the most recent snowfall event. In other words, I only want to compute the difference between a pair of survey dates provided the survey is AFTER the most recent recorded snowfall date. Here is some example data:

Survey Time Snowfall end
1/5/13 13:00 1/9/13 1:00
1/10/13 10:40 1/10/13 18:00
1/12/13 9:40 1/15/13 1:00
1/12/13 14:00 1/17/13 12:00
1/12/13 16:00 1/19/13 23:00
1/13/13 10:45 1/20/13 18:00
1/13/13 11:30 1/22/13 21:00
1/15/13 10:00 1/24/13 21:00
1/15/13 10:00 1/31/13 12:00
1/15/13 11:15 1/31/13 23:00

I'm comfortable working with serial date/time and computing differences, I don't know howto ask excel to search through a column to evaluate differences between records based on a condition. Any thoughts on the simplest way to proceed?

Much thanks!

**QuantumPolagnus** · 10-09-2013, 11:29 PM

You could use an IF() statement to make it conditionally display the time elapsed only if the survey takes place after the last surveyed snowfall event ended. Assuming "Survey Time" is A1 and "Snowfall End" is B1, you could use this formula:

=IF(A3>B2,A3-B2," ") *Note, there is a space between the quotation marks*

Type that into the cell to the right of the second data point (C3) and drag the formula down to populate the list.

I don't know if Excel would display the results in Days or what (I've never dealt with date and time in the same cell), but hopefully that will point you in the right direction.