Excel VB question...Please assist [RESOLVED]

[Myriad_Rocker]

I will try to explain this as well as I possibly can. I know most of you will not download files on here in fear of a virus and I understand. So, I will try to explain and illustrate as much as I can.

I have an Excel spreadsheet of...let's say truck drivers. We'll say they deliver for your local FedEx. Each time they pick up a load of packages at the terminal, they have several stops to drop all of those packages off. On the spreadsheet, I have driver name, load number, stop number, arrival date, arrival time, depart date, depart time, total time, total time in mins, & stop mins.

Now, let me explain what each one is...

Driver Name - Obviously the drivers name, or driver number, if you will

Load Number - This is the number assigned by the terminal for that load or haul

Stop Number - The number of stops, or deliveries per load. In other words, each time the FedEx guy drops a package at a house, the stop number increases by one. This is already defined and can vary from 3 stops to 10 stops.

Arrival Date - Date the package was delivered. Already populated.

Arrival Time - Time the package was delivered. Already populated.

Depart Date - Date the driver left that stop. Already populated.

Depart Time - Time the driver left that stop.

Total Time - The total time it took for that LOAD to be completed. Not that particular stop, but the ENTIRE LOAD. This needs to be calculated.

Total Time In Mins - Same as above except in minutes. This needs to be calculated.

Stop Mins - This is the total difference between arrival time and depart time, taking the date into account. This is also considered "down time." This needs to be calculated.

*all times are in standard military time8

Example Set

**not going to work...I'll post a txt file**

[Myriad_Rocker]

Here's the text file. Let me know if there are any problems with it.

[RobDog888]

You can do it in either an Excel formula or using VB and code calculations.
Would be easier maybe to use Excel, maybe.

I assume that the spreadsheet has multiple groupings and date
ranges?

Your looking for the Formula to do the calcs, right?

[Myriad_Rocker]

It can be done with formulas? How so? How would the formula know how far to take the calc?

[Myriad_Rocker]

Anyone?

[RobDog888]

I'm home now. I loaded the textfile into Excel and I think it just
needs formatting. Give me some time.

Be back.

[Myriad_Rocker]

Keep in mind, that's just example data.

[RobDog888]

Update:
I tried a few different formatting ideas but to no avail.

So, with that said I wanted to know if the solution would be
acceptable in VBA or do you need it in VB?

[Myriad_Rocker]

Thanks for your support!

Question, though...at the cost of sounding like a moron, I'm going to ask you what the difference is between VB and VBA.

[Dave Sell]

Originally posted by Myriad_Rocker
Thanks for your support!

Question, though...at the cost of sounding like a moron, I'm going to ask you what the difference is between VB and VBA.

VB is going to be a stand-alone Visual Basic application, like an EXE, developed in Visual Studio.

VBA is Visual Basic-like code inserted into an Office document like Word, or Excel or Outlook.

[RobDog888]

Thats a legitimate question.

VBA: the VB like development enviroment behind MS Office
applications like Excel, Outlook, Word, etc. It can not compile the
project into exe's. The VBA Project is tied to the front end of Excel
for example. You can reference other Excel VBA projects if they
are not protected or password protected.

VB: the more powerful development enviroment that can create
stand alone applications. It can compile projects into exe's, dll's,
ocx's,etc.

VBA has some of the same functions, methods, and syntax of vb,
thats why people get the two confused.

[Myriad_Rocker]

Okay, that's what I thought.

VBA is fine. Thanks for all your help!

[Myriad_Rocker]

I got it figured out. Total time took a control array formula and the other two were just regular formulas.

Total Time=IF(C2<>99,"",MAX(IF($B$2:$B$1043=B2,$G$2:$G$1043))-MIN(IF($B$2:$B$1043=B2,$E$2:$E$1043)))

Total Time In Mins=IF(H2="","",H2*60*24)

Down Time (Stop Mins)=IF(I2="","",(SUMIF($B$2:$B$1043,B2,$G$2:$G$1043)-SUMIF($B$2:$B$1043,B2,$E$2:$E$1043))*24*60)

Thanks, though.

[RobDog888]

Ok, does it work if the rows change and does it work across multiple groupings?

Looks like your getting sums for the whole sheet? 2 - 1043 rows?