+ Reply to Thread
Results 1 to 8 of 8

Adding time logs from a GPS device

  1. #1
    Registered User
    Join Date
    09-21-2008
    Location
    FL
    Posts
    4

    Adding time logs from a GPS device

    Hello,



    Our company uses GPS data loggers to track workers in the field. our workers distribute flyers and are currently paid by the piece, soon to be paid by the hour. To be able to accurately pay them hourly, we need to be able to efficiently and accurately add up their total time worked and time not working (i.e. standing still) from start to finish. to achieve this, I was thinking that excel could count the total time between logs each day. Then, to calculate time not working within that total, excel could count time logs further apart than "XX" amount of seconds as time not working and provide us in the end with 3 totals. Total time per day working, total actual time working and total time not working. I've attached a sample of one of our GPS's logs in excel format. Is there any way of doing what we need with this data?



    Thank you advance for your time and help.
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Welcome to the forum.

    All the times are exactly 30s apart; how does the sample data illustrate your narrative?

    If you want help with Excel, please post workbooks, not pictures, PDFs, Word documents, ...
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    09-21-2008
    Location
    FL
    Posts
    4
    The GPS device can be set to log at any interval of time (i.e. 10 second, 20 seconds, 30 seconds etc...) Our problem is that if the device is turned off (i.e. if the person takes a break) or the speed column reads anything less than 1, we need to count that time as the person not working.

    I attached the original sample excel file for you review.

    I'm not sure what you mean by "how does the sample data illustrate your narrative?"
    Attached Files Attached Files

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    After the unit is turned off, the first speed will be 0?

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    good grief whatever happened to trust? and the work ethic.
    apart from that tho! how does speed # less than 1 mean not working? i take it the flyer has to be put somewhere and the person stops to do it?
    can you as requested put this in excel first?
    oh i see it is sorry

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Your data seems anomalous. In the first two lines, the carrier has moved 14km in 31s, yet the speed is shown as 0.017 km/hr.

    To a rough (but not bad) approximation, distance = sqrt( (lat2 - lat1)^2 + (lon2-lon1)^2 * cos(lat1).

    None of the calculated speeds are anywhere close to yours ...

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    i agree i get it as
    14.1260 km in 31 seconds
    bleeding fast that

  8. #8
    Registered User
    Join Date
    09-21-2008
    Location
    FL
    Posts
    4
    for some reason, the distance feature does not work. Don't know why though, but it really does not matter as we do not use distance to verify any of the work done.

    first off..."shg After the unit is turned off, the first speed will be 0? "

    ANS: Yes the speed will be zero until the unit gets a fix and begins tracking which is annotated by the column labeled "Valid"

    Second... "martindwilson apart from that tho! how does speed # less than 1 mean not working? i take it the flyer has to be put somewhere and the person stops to do it?"

    ANS: since we are paying the distributor by the hour, motion (walking speed not driving) = work. Our distributors go door to door so I do understand what you are saying about having to stop to place a flyer on the door. we will most likely do some sort of percentage of stopped time and label that as "not distributing". Paying by the hour is still a new concept we are dealing with and thus need a lot of tweaking.

    Please let me konw if you guys need any more info

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. substract two time logs based on a string
    By CptLimaBean in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-11-2008, 11:23 AM
  2. Having trouble keeping entering time on a time sheet
    By cranetech in forum Excel General
    Replies: 1
    Last Post: 06-23-2008, 02:16 PM
  3. Adding Time: What code should I use?
    By stoney1977 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2008, 12:39 AM
  4. Time w/in Each Hour of Day btwn a Time Range
    By gbrogmus in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-30-2007, 03:01 AM
  5. Adding a column in 5000 excel file at a time
    By arehmanbtc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-19-2007, 09:38 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1