Macro or Code to add time to a column of existing times

[slatten]

I have a problem where I have a column of a set original date and times. I need to be able to add a fluctuating amount of time to each of those existing date/times.Below you will see how I have this setup. The placard is referring to an aircraft and Trip 1 is their original trip date and time. On another sheet I am tracking the flight time for each of the placards. As the flight times change I need to be able to generate a new time for trip 2, trip 3 and on and on but the original trip times need to remain the same. Any help would be appreciated.


Placard Trip 1 Trip 2 Trip 3
1001AA 8/8/2015 8:00
1002AA 8/8/2015 8:00
1003AA 8/8/2015 8:00
1004AA 8/8/2015 8:00
1005AA 8/8/2015 8:00
1006AA 8/8/2015 8:00
1007AA 8/8/2015 8:00
1008AA 8/8/2015 8:00
1009AA 8/8/2015 8:00
1010AA 8/8/2015 8:00

[Bernie Deitrick]

Not enough detail, but maybe something like

=B2+VLOOKUP(A2,OtherSheet!A:B,2,False)

[slatten]

Not sure that would work. Let me provide more detail. On one sheet there will be a list of all aircraft along with an associated flight time. It's easy enough to calculate that first trip based on their initial flight time. My problem comes in with the fact that the flight times may change due to the fact that the aircraft may be going to a different destination on Trip 2. As such, I plan to alter the flight time on the original sheet. So, I essentially need to be able to create new trip times for trip 2 without changing the times from trip 1. I'm hoping that makes sense. And maybe my initial approach isn't what I need, perhaps there is a better way? I've just run into a lot of brick walls here. Thanks again.

[Bernie Deitrick]

I didn't really expect that it would work, and can only repeat that you have not given enough detail: what is on the first sheet, what is on the second sheet, and what do you expect to have as the result of the formula? Give cell ranges and a couple of examples of before and after.

[slatten]

Sorry. I've attached the sheet I'm working with. The tab called "Dashboard" lists out all available aircraft in column A along with their initial "known" trip times. Trip time is the time it takes the aircraft to leave the departure point until they are back and available to pick up another person. Also on this tab is the date/time of the initial Trip or Trip 1. On the second tab called "TripPlanner" is the same list of aircraft in column A along with multiple "expected" trips stretched out among the columns along with "patient" which is the name of the passenger. What I am trying to do here is create a planning manifest that I can essentially pre-plan trips and assign patients or passengers to flights ahead of time. The problem I am running into is that the trip time of the aircraft is not a constant variable, it can change which in turn would change the subsequent Trip time for instance: For 1001AA, Trip 1 may be 6/5/2015 11:30 with a trip time of 3 hours putting them available for Trip 2 at 6/5/2015 14:30. If their trip time was always 3 hours it would be easy enough to extend those predicted times out across the board but say Trip 3 orders them to a different destination which takes 4 hours instead. I need to be able to quickly recalculate all subsequent trip times based on the new 4 hour block without altering the Trip 1 and 2 times. I was hoping for a macro or something to that effect that could do all aircraft at once. Is this making more sense. Is that enough detail. Sorry to be vague, the bosses have given me a nearly impossible task that I am trying to make possible. Thanks again.

[Bernie Deitrick]

There needs to be an example of what you have, how it would change, and what you want as the result: there is no indication of how the sheet is used, since the entire sheet is blank except for the row and column headers.

[slatten]

TripPlanner.xlsx

Ok, I've added some sample data. You will see that the "Dashboard" tab has not changed. However, the "TripPlanner" tab now has estimated departure times for all aircraft based upon the trip time on the "Dashboard" tab along with some fictional patients in the first patient column. Now, each of the subsequent trips are calculated based upon the same trip time from "Dashboard" tab. But suppose some of those trip times need to be changed because they have to fly to different destinations? I would like to be able to change them on the "Dashboard" tab and then recalculate all empty future trips essentially. So looking at this sheet as is, say I need to change a few of the trip times for trip 2, I need to be able to recalculate those quickly along with all subsequent trips according to the new trip time. Easy enough to do the long way with formulas and what not on the fly but this needs to be built for someone that knows nothing about formulas and can just enter a number and press a button or something to make the calculations. Does that help?

[Bernie Deitrick]

I have added an extra column for each flight where you can enter the hours and minutes of changes, like 1:30 for an hour and a half, or 0:15 for 15 minutes. The default is 0:00.

TripPlanner.xlsx