program/script to get data from Excel
Budget: €30 – €250 EUR
thuis = Home
Mauritz techniek bv / Ambachtsweg, 1271, Huizen (NL) = The office
given collum's:
Start / Stop / Start locatie / Eind locatie
start day / start work / end work / end day / travel time (before work) / travel time (after work) / Time on the office (before work) / time on the office (after work)
#Start day: from the row "start locatie" the first timestamp when that includes "Thuis"
(exlude 00:00 since this is a poll time from the gps not the time i left home)
( so from the first row of the day use the time from collum "Stop"
(see example 1 image)
#Time on the office (before work) :
when the first location to arrive after ... Thuis is "Mauritz techniek bv" or "Ambachtsweg, 1271, Huizen (NL)" than i got the office before going to a client. if i go from ..... Thuis to another adress the value becomes 0 since i did not visit the office.
if i do go from .... Thuis to the office we need to calculate the time i been there as following
"'Start' value from 'travel time (before work)'" - "'Stop value from 'Start day'
#travel time (before work): Time value from the office or home (see top of page for values) to any other adress (clients)
with these values calculate travel time (Stop - Start = travel time)
based on the example 09:03-08:59= 4
so travel time (before work) becomes : 4
(see example 3)
#Start work: "stop" value from the row of travel time since that is the arrival.
(see example 4)
#End work: We first need to know our last drive this will always be to home or the office so we check for the latest drive to the office or home. if the latest is home we must check if we went to the office before that to calculate "#time on the office (after work)" but for "End work" it does not matter if we went to the office or not we just need the latest time we where at a client. we need the latest client drive (see example 5) and from that row we use the "start" value since we start our drive than what means we stopped working.
#travel time (after work) :
Time value from the latest drive from our latest client to home or the office.
with these values calculate travel time (Stop - Start = travel time)
based on the example 16:28-16:04= 24
so travel time (after work) becomes : 24
(see example 5&6)
#time on the office (after work) :
If we end our day as following latest client > office > home we need to calculate the time we spend on the office to do this we must check if the day has a drive with the values "Mauritz Techniek bv" to "..... Thuis" and take the start value of that drive (see example 7) and the stop value from our latest drive to the office (see example 6)
17:01-16:17=44
so time on the office (after work) becomes 44
#End Day:
Our latest drive from any location (but not from the same, since at 23:59 the gps will make a poll fake row that must not be used.) to ".... Thuis" from that row the "stop" value (see example 8)
Mauritz techniek bv / Ambachtsweg, 1271, Huizen (NL) = The office
given collum's:
Start / Stop / Start locatie / Eind locatie
start day / start work / end work / end day / travel time (before work) / travel time (after work) / Time on the office (before work) / time on the office (after work)
#Start day: from the row "start locatie" the first timestamp when that includes "Thuis"
(exlude 00:00 since this is a poll time from the gps not the time i left home)
( so from the first row of the day use the time from collum "Stop"
(see example 1 image)
#Time on the office (before work) :
when the first location to arrive after ... Thuis is "Mauritz techniek bv" or "Ambachtsweg, 1271, Huizen (NL)" than i got the office before going to a client. if i go from ..... Thuis to another adress the value becomes 0 since i did not visit the office.
if i do go from .... Thuis to the office we need to calculate the time i been there as following
"'Start' value from 'travel time (before work)'" - "'Stop value from 'Start day'
#travel time (before work): Time value from the office or home (see top of page for values) to any other adress (clients)
with these values calculate travel time (Stop - Start = travel time)
based on the example 09:03-08:59= 4
so travel time (before work) becomes : 4
(see example 3)
#Start work: "stop" value from the row of travel time since that is the arrival.
(see example 4)
#End work: We first need to know our last drive this will always be to home or the office so we check for the latest drive to the office or home. if the latest is home we must check if we went to the office before that to calculate "#time on the office (after work)" but for "End work" it does not matter if we went to the office or not we just need the latest time we where at a client. we need the latest client drive (see example 5) and from that row we use the "start" value since we start our drive than what means we stopped working.
#travel time (after work) :
Time value from the latest drive from our latest client to home or the office.
with these values calculate travel time (Stop - Start = travel time)
based on the example 16:28-16:04= 24
so travel time (after work) becomes : 24
(see example 5&6)
#time on the office (after work) :
If we end our day as following latest client > office > home we need to calculate the time we spend on the office to do this we must check if the day has a drive with the values "Mauritz Techniek bv" to "..... Thuis" and take the start value of that drive (see example 7) and the stop value from our latest drive to the office (see example 6)
17:01-16:17=44
so time on the office (after work) becomes 44
#End Day:
Our latest drive from any location (but not from the same, since at 23:59 the gps will make a poll fake row that must not be used.) to ".... Thuis" from that row the "stop" value (see example 8)