Here's what I need:
1. Sheet A contains the following columns: Date, Time, Co, Type, Pt, Address
2. A date range will be entered on Sheet B and Sheet C (like this for example: 9/1/16 - 9/15/16). If the date on Sheet A is on or within the range, it will be included on Sheet B and Sheet C
2) if "Co" on Sheet A is one type, the row/entry will be included on Sheet B. If "Co" is another type, the row/entry will go on Sheet C
3) On Sheet B and Sheet C, miles will be calculated from address a to address b. if entry is the same day, the total miles will be added for the day, minus 35 miles in the last column
4) I have the following formula that calculates distance in miles but sometimes produces errors:
The formula is in column G
F12 is address a
F13 is address b
Below is a Sheet B Sample:
DATE TIME Co TYPE PT NAME ADDRESS MILES DAILY
10/5 Start 123 ABC St, Los Angeles, CA, USA
10:00am – 11:00am D NONADMIT GG 733 A st, Indio, CA 92201, United States 145
12:30pm – 2:30pm D SOC PH 321 3rd St, Los Angeles, CA, USA 126
4:45pm – 6:45pm D SOC SR 999 X st, Oceanside, CA 92057, USA 84
End 123 ABC St, Los Angeles, CA, USA 105 426
10/11 Start 123 ABC St, Los Angeles, CA, USA
9:00am – 11:00am D SOC DK 321 DEF St, Los Angeles, CA, USA 35
2:30pm – 4:00pm D NONADMIT YY 3520 X Dr, Mira Loma, CA 91752, USA #ERROR!
End 123 ABC St, Los Angeles, CA, USA 402 #ERROR!
This is a one time, fixed price job.