• 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.

Calculating a 'Time Between' Exception

S

Stolireef

New Member
First, please accept my appreciation for this site. Although I have just registered, I've lurked for a little while and you all provide a great resource. Hopefully, my Excel skills have improved to the point where I can be a contributor.


Now for my question. I manage a substantial staff of employees and have the ability to track their entries into our database. As a rule, they should be making entries not more than a few minutes apart throughout the day. I'd like to be able to run either a formula or a macro on the spreadsheet of these entries that shows when more than perhaps five minutes have elapsed between entries.


The data I receive from my database is in four columns. Column one is our file number. Column two is the date of the entry. Column three is the time of the entry (in what appears to be acceptable Excel formatting). Column four is the actual note entered on our system.


My goal is to be able to rapidly identify gaps between entries of more than five minutes. The best result would show the time entries around a larger gap in bright red. This wouyld be a great tool for anyone that manages a large staff of people who input data.


Any advice would be greatly appreciated and I will do my level best to reciprocate when I find something useful to contribute.
 
Stolireef


Firstly, Welcome to the Chandoo.org Forums


You should be able to run a DB Query where you extract data for the past period (x Minutes or Hours) then simply run some VBA code over the extracted data and highlight where an entry is >5Min more than the previous entry
 
Hello Stolireef,


If you already have the data in Excel, assuming your time data is in column C, you could put the following formula as a conditional format for the entire column C:

=IFERROR(TEXT(OFFSET(C1,0,0)-OFFSET(C1,-1,0), "hh:mm")>"00:05", FALSE)


(I was expecting simple Time math to work out... but for some reason, Excel is not producing consistent results when doing Time math. Perhaps someone can explain any quirks to watch out for when performing Time math. To work around those anomalies, the formula converts the time to text and then performs a comparison.)


Cheers,

Sajan.
 
Hi Sajan ,


Can you post any data where simple time arithmetic does not work ? I assume you are using the TIMEVALUE function to compare the times to ?


If we wish to highlight both entries , when one entry is different from the preceding or the next by more than 5 minutes , then does a simple OR((C2-C1)>TIMEVALUE("00:05"),(C3-C2)>TIMEVALUE("00:05")) not work ?


Another point to be seen is if the dates will all be of the same day ; if not , we need to consider the date and time together.


Narayan
 
Hi Narayan,

Here is some Time data that did not behave as I was expecting. Any insights you can offer would be great.

[pre]
Code: 
Row	Time	   R2-R1	        Value
1	1:15 PM	   -----
2	1:20 PM	   12:05 AM	0.00347222222222221
3	1:55 PM	   -----
4	2:00 PM	   12:05 AM	0.00347222222222232
[/pre]

As you can see, even though 1:20pm-1:15pm and 2:00pm-1:55pm both return 12:05am as the difference (i.e. 5 minutes), the actual decimal value is different.


Also, this is different from the result from =TIMEVALUE("12:05 am") which returns 0.00347222222222222


Thanks,

Sajan.
 
Hi Sajan ,


We had a discussion on something similar here :


http://chandoo.org/forums/topic/why-does-rounding-create-inaccurate-sums


I think that the problem is one of representing numbers accurately in floating point format , which is how Excel does it.


If you see the actual numeric values ( using the Number format , and at least 15 decimal places ) , the difference between 13:59:59 and 14:00:00 , which is 1 second , is 0.0000115... ; what I want to say is that we are thinking in terms of seconds and minutes , but we are asking Excel to do arithmetic down to probably microseconds.


Clearly , when we are comparing numbers , Excel will use its full precision of 15 decimal places , and there can be a difference in the 14th or 15th decimal digit. But when we compare times , even one second will make a difference in the 5th decimal place ; so when we wish to see whether the difference between two times is less than 5 minutes , since the value of a time of 5 minutes is 0.003473 , I think it is better to create a named range called FIVE_MINS , and put in a value of 0.003473 in it. Any difference between two times which is less than FIVE_MINS is OK. As a comparison , these are some times and their numeric values :

[pre]
Code: 
0:04:59	0.0034606481481481500000000
0:05:00	0.0034722222222222200000000
0:05:01	0.0034837962962963000000000
0:05:59	0.0041550925925925900000000
0:06:00	0.0041666666666666700000000
[/pre]
When we say a time difference is more than 5 minutes , do we mean it is 00:05:01 or more , or do we mean it is 00:06:00 or more ?


Depending on the answer , we can change the value of FIVE_MINS to 0.00348 or even 0.00417.


Ultimately , if we want a mathematically accurate answer , I think we have to do all calculations in terms of numbers rather than times which are not so precise really , though for us , a second is also very precise !


Narayan
 
Thanks Narayan. Good information and good links.


It is just that I expected the same number of microseconds (or whatever the smallest time value could be) to be between two similar periods of time. (i.e. same x number of time units between 1:50am-1:55am and 1:45am-1:50am.)


I understand your comment about rounding errors. I will remember to use ROUND or something similar when I want predictable values.


Thanks,

-Sajan.
 
Sorry I was away from this for a few days. Emergencies do arise. This is a great discussion and very helpful. It's my primary project this week and I'll try out these suggestions. Thanks again for a great site and great assistance.
 
Oh, and by the way. Although I'd love to manage my staff by the microsecond, I think that might be a little overkill. Minutes are fine as the quanta.
 
Remembering that there are 86,400 seconds in a day

Times to the second are hence stored as approx 1/100,000th of a day

so you will need to round to 6 decimal places
 
You must log in or register to reply here.
Back
Top