Excel formula time elapsed

Lleucu

The problem:

These are the train times of departure from Station A:
6.56,11.01,14.58,19.18

The train arrives 31 minutes later at Station B.

The times of return are:
8.45 12.01 16.59 21.19

Client C could be offered an appointment at Job Centre Plus anytime between 9 and 5 for up to 1 hour.

Is there anyway on Excel of calculating the length of the journey at say half hour intervals.

This is for a welfare rights problem to convince the DWP to stop making Client C wait around for several hours for a train (which has happened) thanks

skintpaul

surely journey time is the same? Or am I missing something..?

(Need to factor in the walking time to/from station to JCP, too!)

Nick_C

You don't need any special formulae. Times are just fractions of a day, formatted as hh:mm. Dates are integers counting from 1 January 1900.

Lleucu

Sorry I meant the total journey time.

Issue is that if it exceeds 4 hours, DWP will allow postal signing. The variability is the time of the appointment at the Job Centre. So if for example the appointment is at 09.30, the claimant would need to take a train at 6.56. They would arrive at 7.30 and have to wait 2 hours. The appointment would finish at 10.30 and then the first train home is at 12.01 arriving at 12.32. Total time = 5.5 hours.

agrinnall

You don't need a formula. Enter the start time in A1, the end time in B1, and in C1 enter =B1-A1

e.g.

06:56 12:32 result 05:34

System

Hi

My thoughts are that there are only 3 primary times. IE:
you wouldn't get a return train at 08:45, too early. Similarly the outward at 19:18 is too late.


So it is the optimum of three possibilities

out at 06:56 return at 12:01 plus 31 minutes.
out at 11:01 return at 16:59 plus 31 minutes.
out at 14:58 return at 21:19 plus 31 minutes.

So 06:56 upto 12:32 is 5hrs 36 minutes
So 11:01 upto 17:30 is 6hrs 29 minutes
So 14:58 upto 21:50 is 6hrs 52 minutes

11:01 to 12:32 leaves 1 hour, and this is possible on paper, but unlikely. Allows 1 hour to leave and return to station and sign on. Total time 2 hrs 2 mins.

Out at 14:58, return at 17:30 allows 2 hours to achieve same. So it is possible if the walking and appointment duration are all synchronised. Total time 3 hrs 3 mins.

Not really a spreadsheet kind if thing, this could be expanded and presented in a letter in the hope of achieving the desired end result.

bod1467

The minimum time (1st train to 2nd train) is 2 hours 1 minute (2:01) for an appointment at 15:30. (Actually 2:32 for the train to arrive back at Station A). And even that assumes the Job Centre is right next to the train station and can be reached within 1 minute.

Every other appointment time makes the duration over 5 hours, with 5:36 being the shortest.

Lleucu

all done with your help! Results very awful, long waiting times and very long journey too.