Formulae: Identify date/time in the next 60 minutes

nash · November 24, 2014 4:15AM

Hi, I have a date/time column. I would like to find out if there have been any other date/time within the next 60 minutes:

Date Result
15.05.2014 15:05:55 no
15.05.2014 17:10:55 yes
15.05.2014 17:20:55 no

I tried a similar logic as for moving average, but it didn’t work. I am not sure why. Maybe the date/time format works differently.

Bart · November 24, 2014 7:52AM

Hi,

Please find the solution attached. It might be bit messy as I am not script person but it should do the job.

I have created new column where I am calculating time in hours from 1st of Jan 2014 to every date. Next I took all results into array. Each of the element in array is compared to the value in the cell. If value is more than 0 (not the same date) and less or equal to 1(60 minutes) then I said to put 1 against it. At the end I am summing all results, so the total above 0 means that there is a date within 60 minutes in the data from the given date and time).

Bart

tjbate · November 26, 2014 8:08AM

Natascha

You do not need to use a script to compare values within a column. You can do the same by ranking the column values, and using a subset function to test the record ranking before and the record ranking after. If the difference in hours (DATEDIFF) is less than 1 hour either way, you use an IF statement to write the values "Yes" or "No"

I have broken the formula into parts to aid comprehension, but you can combine the parts to write the Formula as a single Formula column.

Please see example attached.

Welcome!

Categories

Ideas Parade

Tagged