Previous Topic Next Topic

Find a Row where date is between 2 dates

Closed
Roy - Mar 10, 2010 at 11:37 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 12, 2010 at 09:32 AM
Hello,

in MS Excel 2007/2003

I need to find the Rate in effect for a give date, Distance, and Vehicle Type


Effective Date Expiry Date Distances Vehicle Type Rate
2007-03-01 2007-03-18 000.00 KM TO 010.00 KM 5 AXLE S/T $4.42
2007-03-01 2007-03-18 000.00 KM TO 010.00 KM 6 AXLE S/T $4.69
2007-03-01 2007-03-18 000.00 KM TO 010.00 KM 7 AXLE B TRAIN $7.55
2007-03-01 2007-03-18 000.00 KM TO 010.00 KM 8 AXLE B TRAIN $9.29
2007-03-01 2007-03-18 000.00 KM TO 014.00 KM 5 AXLE S/T $6.19
2007-03-01 2007-03-18 000.00 KM TO 014.00 KM 6 AXLE S/T $6.56
2007-03-01 2007-03-18 000.00 KM TO 014.00 KM 7 AXLE B TRAIN $10.56
2007-03-01 2007-03-18 000.00 KM TO 014.00 KM 8 AXLE B TRAIN $13.01
2007-03-01 2007-03-18 000.00 KM TO 017.00 KM 5 AXLE S/T $7.52
2007-03-01 2007-03-18 000.00 KM TO 017.00 KM 6 AXLE S/T $7.97
2007-03-01 2007-03-18 000.00 KM TO 017.00 KM 7 AXLE B TRAIN $12.83
2007-03-01 2007-03-18 000.00 KM TO 017.00 KM 8 AXLE B TRAIN $15.02
2007-03-01 2007-03-18 000.00 KM TO 040.00 KM 5 AXLE S/T $17.80
2007-03-01 2007-03-18 000.00 KM TO 040.00 KM 6 AXLE S/T $18.86
2007-03-01 2007-03-18 000.00 KM TO 040.00 KM 7 AXLE B TRAIN $30.37
2007-03-01 2007-03-18 000.00 KM TO 040.00 KM 8 AXLE B TRAIN $37.39
2007-03-01 2007-03-18 000.00 KM TO 070.00 KM 5 AXLE S/T $30.94
2007-03-01 2007-03-18 000.00 KM TO 070.00 KM 6 AXLE S/T $32.78
2007-03-01 2007-03-18 000.00 KM TO 070.00 KM 7 AXLE B TRAIN $52.79
2007-03-01 2007-03-18 000.00 KM TO 070.00 KM 8 AXLE B TRAIN $65.00
2007-03-01 2007-03-18 070.00 KM TO 122.00 KM 5 AXLE S/T $-
2007-03-01 2007-03-18 070.00 KM TO 122.00 KM 6 AXLE S/T $-
2007-03-01 2007-03-18 070.00 KM TO 122.00 KM 7 AXLE B TRAIN $-
2007-03-01 2007-03-18 070.00 KM TO 122.00 KM 8 AXLE B TRAIN $-
2007-03-01 2007-03-18 009.30 KM TO 010.00 KM 5 AXLE S/T $0.31
2007-03-01 2007-03-18 009.30 KM TO 010.00 KM 6 AXLE S/T $0.33
2007-03-01 2007-03-18 009.30 KM TO 010.00 KM 7 AXLE B TRAIN $0.53
2007-03-01 2007-03-18 009.30 KM TO 010.00 KM 8 AXLE B TRAIN $0.65
2007-03-01 2007-03-18 009.30 KM TO 014.00 KM 5 AXLE S/T $2.08
2007-03-01 2007-03-18 009.30 KM TO 014.00 KM 6 AXLE S/T $2.20
2007-03-01 2007-03-18 009.30 KM TO 014.00 KM 7 AXLE B TRAIN $3.55
2007-03-01 2007-03-18 009.30 KM TO 014.00 KM 8 AXLE B TRAIN $4.37
2007-03-01 2007-03-18 009.30 KM TO 017.00 KM 5 AXLE S/T $3.40
2007-03-01 2007-03-18 009.30 KM TO 017.00 KM 6 AXLE S/T $3.61
2007-03-01 2007-03-18 009.30 KM TO 017.00 KM 7 AXLE B TRAIN $5.81
2007-03-01 2007-03-18 009.30 KM TO 017.00 KM 8 AXLE B TRAIN $7.15
2007-03-01 2007-03-18 009.30 KM TO 040.00 KM 5 AXLE S/T $12.27
2007-03-01 2007-03-18 009.30 KM TO 040.00 KM 6 AXLE S/T $13.00
2007-03-01 2007-03-18 009.30 KM TO 040.00 KM 7 AXLE B TRAIN $20.94
2007-03-01 2007-03-18 009.30 KM TO 040.00 KM 8 AXLE B TRAIN $25.78
2007-03-01 2007-03-18 009.30 KM TO 070.00 KM 5 AXLE S/T $25.41
2007-03-01 2007-03-18 009.30 KM TO 070.00 KM 6 AXLE S/T $26.93
2007-03-01 2007-03-18 009.30 KM TO 070.00 KM 7 AXLE B TRAIN $43.36
2007-03-01 2007-03-18 009.30 KM TO 070.00 KM 8 AXLE B TRAIN $53.39
2007-06-01 2007-11-30 000.00 KM TO 010.00 KM 5 AXLE S/T $2.79
2007-06-01 2007-11-30 000.00 KM TO 014.00 KM 5 AXLE S/T $3.91
2007-06-01 2007-11-30 000.00 KM TO 040.00 KM 5 AXLE S/T $11.23
2007-06-01 2007-11-30 000.00 KM TO 070.00 KM 5 AXLE S/T $19.52
2007-06-01 2007-11-30 070.00 KM TO 122.00 KM 5 AXLE S/T $-
2007-06-01 2007-11-30 009.30 KM TO 010.00 KM 5 AXLE S/T $0.20
2007-06-01 2007-11-30 009.30 KM TO 014.00 KM 5 AXLE S/T $1.31
2007-06-01 2007-11-30 009.30 KM TO 017.00 KM 5 AXLE S/T $2.15
2007-06-01 2007-11-30 009.30 KM TO 040.00 KM 5 AXLE S/T $8.57
2007-06-01 2007-11-30 009.30 KM TO 070.00 KM 5 AXLE S/T $16.93
2007-06-01 2007-11-30 000.00 KM TO 010.00 KM 6 AXLE S/T $2.96
2007-06-01 2007-11-30 000.00 KM TO 014.00 KM 6 AXLE S/T $4.14
2007-06-01 2007-11-30 000.00 KM TO 040.00 KM 6 AXLE S/T $11.83
2007-06-01 2007-11-30 000.00 KM TO 070.00 KM 6 AXLE S/T $20.70
2007-06-01 2007-11-30 070.00 KM TO 122.00 KM 6 AXLE S/T $-
2007-06-01 2007-11-30 009.30 KM TO 010.00 KM 6 AXLE S/T $0.21
2007-06-01 2007-11-30 009.30 KM TO 014.00 KM 6 AXLE S/T $1.39
2007-06-01 2007-11-30 009.30 KM TO 017.00 KM 6 AXLE S/T $2.28
2007-06-01 2007-11-30 009.30 KM TO 040.00 KM 6 AXLE S/T $9.08
2007-06-01 2007-11-30 009.30 KM TO 070.00 KM 6 AXLE S/T $17.94
2007-06-01 2007-11-30 000.00 KM TO 010.00 KM 7 AXLE B TRAIN $4.76
2007-06-01 2007-11-30 000.00 KM TO 014.00 KM 7 AXLE B TRAIN $6.67
2007-06-01 2007-11-30 000.00 KM TO 040.00 KM 7 AXLE B TRAIN $19.05
2007-06-01 2007-11-30 000.00 KM TO 070.00 KM 7 AXLE B TRAIN $33.33
2007-06-01 2007-11-30 070.00 KM TO 122.00 KM 7 AXLE B TRAIN $-
2007-06-01 2007-11-30 009.30 KM TO 010.00 KM 7 AXLE B TRAIN $0.33
2007-06-01 2007-11-30 009.30 KM TO 014.00 KM 7 AXLE B TRAIN $2.24
2007-06-01 2007-11-30 009.30 KM TO 017.00 KM 7 AXLE B TRAIN $3.67
2007-06-01 2007-11-30 009.30 KM TO 040.00 KM 7 AXLE B TRAIN $14.62
2007-06-01 2007-11-30 009.30 KM TO 070.00 KM 7 AXLE B TRAIN $28.89
2007-06-01 2007-11-30 000.00 KM TO 010.00 KM 8 AXLE B TRAIN $5.86
2007-06-01 2007-11-30 000.00 KM TO 014.00 KM 8 AXLE B TRAIN $8.21
2007-06-01 2007-11-30 000.00 KM TO 040.00 KM 8 AXLE B TRAIN $23.45
2007-06-01 2007-11-30 040.00 KM TO 070.00 KM 8 AXLE B TRAIN $41.04
2007-06-01 2007-11-30 070.00 KM TO 122.00 KM 8 AXLE B TRAIN $-
2007-06-01 2007-11-30 009.30 KM TO 010.00 KM 8 AXLE B TRAIN $0.41
2007-06-01 2007-11-30 009.30 KM TO 014.00 KM 8 AXLE B TRAIN $2.76
2007-06-01 2007-11-30 009.30 KM TO 017.00 KM 8 AXLE B TRAIN $4.51
2007-06-01 2007-11-30 009.30 KM TO 040.00 KM 8 AXLE B TRAIN $0.24
2007-06-01 2007-11-30 009.30 KM TO 070.00 KM 8 AXLE B TRAIN $35.57
2007-06-01 2007-11-30 000.00 KM TO 017.00 KM 5 AXLE S/T $4.74
2007-06-01 2007-11-30 000.00 KM TO 017.00 KM 6 AXLE S/T $5.03
2007-06-01 2007-11-30 000.00 KM TO 017.00 KM 7 AXLE B TRAIN $8.09
2007-06-01 2007-11-30 000.00 KM TO 017.00 KM 8 AXLE B TRAIN $9.97
2008-06-01 2008-11-30 000.00 KM TO 010.00 KM 5 AXLE S/T $3.54
2008-06-01 2008-11-30 000.00 KM TO 010.00 KM 6 AXLE S/T $3.75
2008-06-01 2008-11-30 000.00 KM TO 010.00 KM 7 AXLE B TRAIN $6.04
2008-06-01 2008-11-30 000.00 KM TO 010.00 KM 8 AXLE B TRAIN $7.43
2008-06-01 2008-11-30 000.00 KM TO 014.00 KM 5 AXLE S/T $4.95
2008-06-01 2008-11-30 000.00 KM TO 014.00 KM 6 AXLE S/T $5.25
2008-06-01 2008-11-30 000.00 KM TO 014.00 KM 7 AXLE B TRAIN $8.45
2008-06-01 2008-11-30 000.00 KM TO 014.00 KM 8 AXLE B TRAIN $10.40
2008-06-01 2008-11-30 000.00 KM TO 017.00 KM 5 AXLE S/T $6.01
2008-06-01 2008-11-30 000.00 KM TO 017.00 KM 6 AXLE S/T $6.37
2008-06-01 2008-11-30 000.00 KM TO 017.00 KM 7 AXLE B TRAIN $10.26
2008-06-01 2008-11-30 000.00 KM TO 017.00 KM 8 AXLE B TRAIN $12.02
2008-06-01 2008-11-30 000.00 KM TO 040.00 KM 5 AXLE S/T $14.24
2008-06-01 2008-11-30 000.00 KM TO 040.00 KM 6 AXLE S/T $15.09
2008-06-01 2008-11-30 000.00 KM TO 040.00 KM 7 AXLE B TRAIN $24.30
2008-06-01 2008-11-30 000.00 KM TO 040.00 KM 8 AXLE B TRAIN $29.91
2008-06-01 2008-11-30 000.00 KM TO 070.00 KM 5 AXLE S/T $24.75
2008-06-01 2008-11-30 000.00 KM TO 070.00 KM 6 AXLE S/T $26.23
2008-06-01 2008-11-30 000.00 KM TO 070.00 KM 7 AXLE B TRAIN $42.24
2008-06-01 2008-11-30 000.00 KM TO 070.00 KM 8 AXLE B TRAIN $52.00
2008-06-01 2008-11-30 070.00 KM TO 122.00 KM 5 AXLE S/T $24.75
2008-06-01 2008-11-30 070.00 KM TO 122.00 KM 6 AXLE S/T $26.23
2008-06-01 2008-11-30 070.00 KM TO 122.00 KM 7 AXLE B TRAIN $42.24
2008-06-01 2008-11-30 070.00 KM TO 122.00 KM 8 AXLE B TRAIN $52.00
2008-06-01 2008-11-30 009.30 KM TO 010.00 KM 5 AXLE S/T $0.25
2008-06-01 2008-11-30 009.30 KM TO 010.00 KM 6 AXLE S/T $0.26
2008-06-01 2008-11-30 009.30 KM TO 010.00 KM 7 AXLE B TRAIN $0.42
2008-06-01 2008-11-30 009.30 KM TO 010.00 KM 8 AXLE B TRAIN $0.52
2008-06-01 2008-11-30 009.30 KM TO 014.00 KM 5 AXLE S/T $1.66
2008-06-01 2008-11-30 009.30 KM TO 014.00 KM 6 AXLE S/T $1.76
2008-06-01 2008-11-30 009.30 KM TO 014.00 KM 7 AXLE B TRAIN $2.84
2008-06-01 2008-11-30 009.30 KM TO 014.00 KM 8 AXLE B TRAIN $3.49
2008-06-01 2008-11-30 009.30 KM TO 017.00 KM 5 AXLE S/T $2.72
2008-06-01 2008-11-30 009.30 KM TO 017.00 KM 6 AXLE S/T $2.89
2008-06-01 2008-11-30 009.30 KM TO 017.00 KM 7 AXLE B TRAIN $4.65
2008-06-01 2008-11-30 009.30 KM TO 017.00 KM 8 AXLE B TRAIN $5.72
2008-06-01 2008-11-30 009.30 KM TO 040.00 KM 5 AXLE S/T $9.82
2008-06-01 2008-11-30 009.30 KM TO 040.00 KM 6 AXLE S/T $10.40
2008-06-01 2008-11-30 009.30 KM TO 040.00 KM 7 AXLE B TRAIN $16.75
2008-06-01 2008-11-30 009.30 KM TO 040.00 KM 8 AXLE B TRAIN $20.62
2008-06-01 2008-11-30 009.30 KM TO 070.00 KM 5 AXLE S/T $20.33
2008-06-01 2008-11-30 009.30 KM TO 070.00 KM 6 AXLE S/T $21.54
2008-06-01 2008-11-30 009.30 KM TO 070.00 KM 7 AXLE B TRAIN $34.69
2008-06-01 2008-11-30 009.30 KM TO 070.00 KM 8 AXLE B TRAIN $42.71
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 10, 2010 at 12:38 PM
Based on data could you provide what is your expectation to see. Could you use a row to explain a bit more
0
Roy
Mar 10, 2010 at 01:23 PM
I have one sheet with the dates, distances, and vehicle type and another sheet where the use enters the date of their trip, the distance they traveled and the type of vehicle. I need a function that will select the correct rate for that trip.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > Roy
Mar 12, 2010 at 09:32 AM
Sorry but I am still not clear on how the data is to be read
For effective date of 3/1/2007 for veh type 5 AXLE S/T , if a person travels 9.5 km
from your table i see this
000.00 KM TO 010.00 KM
009.30 KM TO 010.00 KM

Now how this 9:30-10 comes into play
0