Excel Question - Time Calculations

[Fingolfin269]

Let's say I know the final time of an event and along the way to this final time there are other times I want to track.

Time F = Final time
Time E = ?
Time D = ?
Time C = ?
Time B = ?
Time A = ?

I know how long it takes to go from F to E, E to D, etc. Sounds easy, right? Well, in between these times are dead zones, but for the sake of this discussion we will just call them breaks.

Let's say Time F = 4 PM and I know it takes 7 hours to go from E to F. One would normally just say 9AM, right? However, let's say there is a 30m break from 2:00PM - 2:30PM and a 15m break from 9:30AM-9:40AM.

How do you take those breaks into account when calculating the previous times?

 

[Fardringle]

Either add additional "steps" for the breaks, or add the time of the breaks onto the actual "work" time for the existing steps.

 

[Fingolfin269]

Originally posted by: Fardringle
Either add additional "steps" for the breaks, or add the time of the breaks onto the actual "work" time for the existing steps.

That's my problem. I think I'll have to add in additional steps if there is no other way because I have to work this out in real time so the breaks cannot be considered as work time.

 

[petesamprs]

Not sure I understand your question but you have to input the size of the "breaks" for excel to know to incorporate them. You also have to tell excel in between which to time snapshots the break occurred.

But once you do that, you don't necessarily need to define the start/finish of the break as new time points. The duration of the break can just be factored into your time formula.

For example, the 45 minutes of breaks in the 7 hours preceding 4PM (Time F) would just mean you modify your formula for time E to be 4pm - (7 hours + 45 minutes). Excel calculates time in Days so as long as the units are consistent it should correctly give you 8:15am for Time E.