I need to develop a VBA program with travel time calculation from app e.g. google map
- or -
Post a project like this2377
£350(approx. $446)
- Posted:
- Proposals: 4
- Remote
- #1765234
- OPPORTUNITY
- PRE-FUNDED
- Awarded
Description
Experience Level: Intermediate
General information for the business: sales staff visiting clients
Kind of development: New program from scratch
Description of requirements/functionality: Requirement
To calculate travelling time and average actual hourly rate for foot/public transport and private vehicle via use google map (or map quest or similar)
Global Variables:
V1) schedule gap minutes e.g. 60 minutes – anything over will be ignored
V2) hourly rate e.g. £10 – multiply totaltime x hourly rate to get total wage
V3) Living wage e.g. £7.50
Input files:
CaregiverExport.csv ( referred as Input 1) (list of caregiver address with post code )
Visit list by Caregiver.xlsx (referred as Input 2) (list of rota per caregiver over a selected period)
Needs to be transparent so can see the work in progress
Output in excel file
1) detail report (see spec for details)
2) summary report (see spec for details)
+++++++++++
Detail requirement:
We need to calculate the sales staff average hourly rate after taken into account of travelling time between different client addresses.
Travelling time does not count when travel to the first client nor returning from the last client.
Travelling time is only counted if the schedule gap of two clients times are less than V1 (schedule gap). If its more then assume the sales staff goes home.
Example 1
Global variable set as:
V1 = 120 min
V2 = £10 per hour
V3= £7.50
Staff1 has rota of client A 9am – 10am then client B 11am – 12pm
The schedule gap between client A and B is 60 minutes, so the travelling time is counted because it is less than V1 (120 min). So the travelling time between A and B are based on given post code and the travelling time will have to be calculated via google app /map quest etc
So actual total hours = worked hours ( 2 hours from the client) + travelling time (20 min)
=2.33 hours
Average hourly rate = total hours at the client (2 hours from client) x 10 (hourly rate)/ actual total
= 2 x 10 / 2.33
= £8.58
Compliance from report = Y if average hourly rate is > V3 else N
Example 2
Global variable set as:
V1 = 120 min
V2 = £8 per hour
V3= £7.50
Staff1 has rota of client A 9am – 10am , client B 11am – 12pm , client C 2:30pm-5:30pm, client D 6pm- 7pm
The schedule gap between A and B is 60 min , so travelling time between A and B will need to be calculated (20min) , the schedule gap between B and C is 150 mins which is larger than V1 (120 min) then it will not be taken into travelling time, the schedule gap between C and D is 30 min which is less than V1 so the travelling time between C and D will be counted (45 minute).
So actual total hours = worked hours ( 6 hours from clients) + travelling time (20min +45 min ) = 7.08 hours
Average hourly rate = total hours at the client (6 hours from client) x 8 (hourly rate)/ actual total
= 6 x 8 / 7.08
= £6.78
Compliance from report = N if average hourly rate is > V3 else N
OS requirements: Windows
Extra notes:
Kind of development: New program from scratch
Description of requirements/functionality: Requirement
To calculate travelling time and average actual hourly rate for foot/public transport and private vehicle via use google map (or map quest or similar)
Global Variables:
V1) schedule gap minutes e.g. 60 minutes – anything over will be ignored
V2) hourly rate e.g. £10 – multiply totaltime x hourly rate to get total wage
V3) Living wage e.g. £7.50
Input files:
CaregiverExport.csv ( referred as Input 1) (list of caregiver address with post code )
Visit list by Caregiver.xlsx (referred as Input 2) (list of rota per caregiver over a selected period)
Needs to be transparent so can see the work in progress
Output in excel file
1) detail report (see spec for details)
2) summary report (see spec for details)
+++++++++++
Detail requirement:
We need to calculate the sales staff average hourly rate after taken into account of travelling time between different client addresses.
Travelling time does not count when travel to the first client nor returning from the last client.
Travelling time is only counted if the schedule gap of two clients times are less than V1 (schedule gap). If its more then assume the sales staff goes home.
Example 1
Global variable set as:
V1 = 120 min
V2 = £10 per hour
V3= £7.50
Staff1 has rota of client A 9am – 10am then client B 11am – 12pm
The schedule gap between client A and B is 60 minutes, so the travelling time is counted because it is less than V1 (120 min). So the travelling time between A and B are based on given post code and the travelling time will have to be calculated via google app /map quest etc
So actual total hours = worked hours ( 2 hours from the client) + travelling time (20 min)
=2.33 hours
Average hourly rate = total hours at the client (2 hours from client) x 10 (hourly rate)/ actual total
= 2 x 10 / 2.33
= £8.58
Compliance from report = Y if average hourly rate is > V3 else N
Example 2
Global variable set as:
V1 = 120 min
V2 = £8 per hour
V3= £7.50
Staff1 has rota of client A 9am – 10am , client B 11am – 12pm , client C 2:30pm-5:30pm, client D 6pm- 7pm
The schedule gap between A and B is 60 min , so travelling time between A and B will need to be calculated (20min) , the schedule gap between B and C is 150 mins which is larger than V1 (120 min) then it will not be taken into travelling time, the schedule gap between C and D is 30 min which is less than V1 so the travelling time between C and D will be counted (45 minute).
So actual total hours = worked hours ( 6 hours from clients) + travelling time (20min +45 min ) = 7.08 hours
Average hourly rate = total hours at the client (6 hours from client) x 8 (hourly rate)/ actual total
= 6 x 8 / 7.08
= £6.78
Compliance from report = N if average hourly rate is > V3 else N
OS requirements: Windows
Extra notes:
Terry C.
100% (22)Projects Completed
10
Freelancers worked with
8
Projects awarded
60%
Last project
14 Jun 2023
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies