Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formulae: Identify date/time in the next 60 minutes - Visokio Forums
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.
  • 2 Comments
  •     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
    Attachments
    dateandtime.iok 11K
  •     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!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership