• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to work out if a time falls within 1 hours either side of another time

C

Christine Hindson

New Member
Hi All, I have a requirement to work out if an agents log in time falls within 1 hour either side of their expected log in time. At the minute I have added 2 additional columns in the tab that shows the expected log in to give me the time 1 hour before and 1 hour after. I then have a very long IF statement that looks at all 3 options to see if it gets a match (based on the the hour). Due to the number of log ins we have this takes an age to calculate so I was wondering if there is an easier way to do this.
 
=AND([login time]>=[expected time]-1/24, [login time]<=[expected time]+1/24)

For example, when expected time is 09:00 the formula returns true when the login time is from 08:00 to 10:00 (+/- 1 hour)
 
Last edited:
Because of the symmetry of the acceptable time differences the two conditions may be combined to give

= ABS( [@[login time]] - [@[expected time]] ) < TIME( 1, 0, 0 )

where the TIME function gives 1/24.
 
You must log in or register to reply here.
Back
Top