logbook help

**gs1** · 06-21-2009, 05:13 AM

hi guys,

I have created a simple vehicle logbook. (see atatchment)

Manually enteering into the 'logbook' tab... Date into column A, Location 'From' in Column B, location 'To' in column C.

Columns D & E are calculations simply combining the data in columns B & C, column D is B + C, and column E is columns C + B. This data is used in the 'lookup table' tab.

Columns F & G are then automatically populated using if...then and vlookup functions from the 'lookup table' tab.

In the 'lookup table' tab i can then manually enter the 'from' 'to' 'distance travelled' and 'toll cost'. and the 'logbook' can then determine the distance travelled in either direction. This part works fine.

As an extension of this, what i want to do is try to identify the 3 & 12 month periods where i have travelled the most distance. I would like this to be displayed in the 'max distance travelled' tab with the date range and the distance.

My problem is that as the amount of entries for any given day is not identical, i am not sure if this can be done.

Any help would be greatly appreciated.

Thanks

Grant

**oldchippy** · 06-21-2009, 05:19 AM

Hi,

It's password protected

**gs1** · 06-21-2009, 05:49 AM

oops...

try this one then!

**oldchippy** · 06-21-2009, 01:53 PM

Hi,

Does this work for you?

In B3 of Max distance travelled

=DATE(YEAR(A3),MONTH(A3)+3,DAY(A3)) then in C3

=VLOOKUP(B3,Logbook!A:J,10,TRUE)-VLOOKUP(A3,Logbook!A:J,9,TRUE)

Then B9

=DATE(YEAR(A9),MONTH(A9)+12,DAY(A9)) then in C9

=VLOOKUP(B9,Logbook!A:J,10,TRUE)-VLOOKUP(A9,Logbook!A:J,9,TRUE)

**gs1** · 06-22-2009, 01:31 AM

that works fine, thanks

Short of copying this formula down for every day of the year, to find the date range with the maximum distance travelled. would there be a function or code that would automatically find the 3 month range in which the maximum distance was travelled? same with 12 month period.

Keeping in mind that a 'month' does not necessarily need to begin on the 1st. (i.e. essentially we are talking about a '~90' day period (depending on the number of days in the month.)

Thanks

Grant