How do I get a time interval from 2 differant rows?

I am trying to create a trip log for tracking trip and tax info. I have receievd some valuable tips from another thread however it has now grown beyond that thread so I will start over. The most recent problem is a round trip consists of more than one entry, I have attached what I have so far with 2 sample trips and an explanation of how the tracking should work. Each destination should track seperately and fill in the corrosponding blanks on the totals sheet except for the "non Working" destination which should be ignored by the meals column and the time columns. Any help would be greatly appreciated. I am using excel2003 but it should be compatible with all the latest versions.

Trip time should be the total round trip time so F6 could be blank and F7 should be 18:45 ((C6-E7 total). Wait time should be G6 blank and G7 9:10 (E6-C7 diff). Meals column should be M6 blank and M7 based on F7 and in this case be 3.

I don't know how to account for the varibles.

Round trips are not always to the same destination and sometiMes there are short trips within a round trip which would use 3 rows, for exampl row 1 would be MJ - SC, row 2 SC-SC, row 3 SC - MJ, so the trip time would go from row 1 start to row 3 end and the meals would be based on the total round trip time.

In the second example F9 & F10 could be blank, F11 should be 19:30 (C9-E11 total). G9 would be blank, G10 should be 4:00 (E9-C10 diff), G11 should be 4:00 (E10-C11 diff). M9 & M10 should be blank and M11 should be 3 (from F11).

previuos thread: http://www.excelforum.com/showthread.php?t=640665

Hi, I hope the attached file is what you want, I have concentrated on cols F & G for the most part but I had to alter formula in col M to take into account when col F was blank(Empty text).
Regards Howard

Hi, I see what you mean! I'll take another look though it may take me some time
regards Howard

It's close Howard, just those pesky variables that screw everything up? Pleas note the fields on the summary page, that is how and where it should ultimately sort.

Hi
I am getting nowhere with this, I am as sure as I can be that it is to do with the date and time formats. When I make an entry on your sheet I appear to be altering your formats. This is way beyond my knowledge of Excel.
Perhaps someone else will help,though I will keep watching the thread to improve my limited skills
regards Howard

Hi Ratcat,
As far as mental blocks go join the club! I'm totally and completely confused now. On your sheet how do I access column P I've tried unhiding and it just will not show itself. The funny farm beckons.
Regards Howard

Hi Ratcat, I like the idea of getting as far away as possible for the formatted cells,Your answers show the total journey time,9:35 as I understand it what Attilla wants is the total time, from departure back at where it started which is 18:45
I'm going back into a darkened room!
Cheers Howard

Hi,
Attilla I think I have finally reached the conclusion that you require(if I haven't I have learned how to calculate time in excel). I formatted cols C & E with custom format dd-mmm-yy h:mm this means you enter the date and time in the one cell which makes cols B & D redundant. The cols F & G which is where the answers go is custom formatted [h]:mm which makes the whole thing work.
I hope this is what you want
regards Howard

Ok I have cleaned it up and it still works as it was. I have posted another actual example, note the times and meals do not calculate in the first example (rows 6-9 is one round trip). I also noticed if a nonworking trip is selected from the drop down several meal fields above it are cleared? Only the onworking trip should exclude meal counts.

Hi, Attilla,
I have rejigged my last attachment deleting the redundant columns and altering the formula in columns L & M so it all calculates ok.

Your last attachment I looked at, for everything to work ok (I should have made this clear) was because you did'nt leave an empty row between each round trip.

Have a look at my attachment leave a row between each round trip if the round trip is 2 rows long it will work, if it is 3 rows it will work, if it is 4 rows it won't. there must be an empty row between trips.

Regards Howard

Hi Attila, In the latest attachment I have adjusted the formula to take in upto 4 rows more can be added by adjusting the formula possibly another 2 taking it upto 6 rows but the way everything is set up a blank row between trips is essential. as far as a single row is concerned this is the first mention of this being a possibility, it's always been a case of a round trip which would take at least 2 rows up.
regards Howard

Hi, Attilla I have made adjustment to cater for a single row calculation but only involving departures or destinations of "Yards" "Switchers" or "Misc Turns". as far as multi row calcs 5 is the maximum.

I had a look at the yearly total sheet and put in formulas for "Moose Jaw","Swift Circuit" and "Broadview", so they calculate the number of trips,total hours, waiting time and meals from the "Jan" sheet.

Regards Howard

I played with it a bit and I found it is not calculating properly now. Perhaps the single row thing is the problem? What I am finding is it picks up to many trips. I ran several trials using MJ as the start and the counter picked up several trips to MJ, it should only pick up one destination per round trip. Even a 3 or 4 line round trip should pick up only once. Perhaps going back to a minimum of 2 lines per trip is the answer?

Hi Attila,
I decided after my last attempt, to try something different, approach the problem from another angle. The title of the thread is "How to get a time interval from two differant rows". then 3 rows then 4 then 1,then possibly upto 8. The point of the question is the time interval you wanted 2 times calculating, the total round trip time (from MJ back to MJ) and the waiting time, the time between arriving at the destination and the time setting off back to MJ. and this was spread over 2,3,4 rows depending on the supplimentary journey's which from the point of view of calculating the respective times were irrelevant,these just confused things.
If I had been designing this layout I would have put everything on one row this makes all the calcs so easy, the supplimentary journeys can still be entered on the same row but out of the way.
So thats what I did the result is attached. there is no blank rows I have made room for 3 supplementary journeys but you can easily add more and it works!
Regards Howard

Hi Attila, Sorry I realised I had not allowed for "Yards","Switchers" or "Misc Turns" I have now.
Howard

I have been palying with your new design and changed a few things and everything still works, I like it. One thing about a single row for a round trip is the miles and income have to be added together before entry as each leg of the trip is independent, I guess I could just add more columns? I will play some more but so far it looks good, thanks Howard.

Hi Attila, I'm pleased you like it, I can relax a little now, I go back to work tomorrow after 10 days holiday. It'll be a nice change!
Best regards Howard

I think I solved the Div/0 error, not sure if this is right but it seems to work,
I changed the formula to =IF(ISERROR(SUM(C18/C16)),0,(SUM(C18/C16)))

Hi Attila,

I have sorted Col R, I've added an extra proviso in case dividing by zero. In col S the meals col,(it wouldn't align in centre), for some reason the entire column was custom formatted (something really wierd) I changed that to number format and that did the trick.


On the Yearly totals Page I put in the formula for calculating Avg time again making a proviso in case of dividing by zero, and updated the formulas for No Trips, and so forth,I also added another sheet and named it "Feb" and carried over the formulas in yearly totals so you could see the difference I did the top 3 and left the rest for you to do.
The Feb sheet is an exact copy of Jan without the values but ofcoursethere is all the fomulas

Best regards Howard

The avg time worked right on, shortened my formula quite a bit. I see in the Trips row you removed the Yearly Totol tag from the formula, can that also be removed from the Meals, Total Time and Wait Time rows?

In column R row 9 there is a side trip listed, the wait time shows as 19:00 hours, the trip time from column Y should be subtracted from column R to reflect a wait time of 12:00 hours.

The current formula is =IF(ISBLANK(L9),"",IF(ISBLANK(K9),0,K9-E9))

I Changed it to =IF(ISBLANK(L9),"",IF(ISBLANK(K9),0,K9-E9-Y9-AH9-AQ9-AZ9)) but it did not work? Extra trip times should be subtracted from the Wait Time to reflect an acurate tally?

Hi Attila,
As I understood the "Wait time" was the time between arriving at destination until the time that the return journey started which is what the formula K9-E9 calculated giving 19 hrs.if the times of supplementary journey's are to be deducted off this time then the complete formula from the top row 6 will be =IF(ISBLANK(L6),"",IF(ISBLANK(K6),0,((K6-E6)-SUM(Y6,AH6,AQ6,AZ6)))). run this down the col. then row 9 will return the answer of 12hrs.

In the attachment I have altered the formulas in the yearly totals page tomake it easier for you to update the formulas as you add monthly sheets, just drag them across.
Regards Howard

Thanks again Howard that mod worked great as always, the differance was when the side trips were added on. I tied that change for the sum page and it too worked, to bad I alreadt did half the sheet by hand?

Two more fixes and all is done, the sum page pay halves and mileage total, sould your prefer I start another thread?

You have been most helpful and I appreciate your expertise, patiance and willingness to assist.

Hi Attila,
I think it should be me thanking you ! I have learned a great deal with this thread I have delved into things that I had no knowledge of,so it has helped me a great deal.

I think a new thread should be started as it is now a totally different question now.
best regards Howard

That's why I thought of starting a new thread as the final 2 fixes are unrelated to this. I too have learned a lot Howard, every change I tried to trace it thru and figure out where it was coming from. You have been a great help and I appreciate it very much. It's amazing not only what this app can do in the right hands but what we can do when challenged!

After much playing around and lots of "off site" help from Gearcutter this log book idea has turned into a very valuable, very useful and very powerful workbook. Thank you very much Gearcutter, for without your assistance this idea would have never come as far as it did. Thanks to those others who contributed ideas.