Logic test to calculate linear time difference crossover midnight - 2nd part not working??

Hi Team.

Using Excel 2007.

I have made some progress on the spreadsheet issue that I was working on earlier. I decided to work on one portion at a time. If I can get the time calculation difference sorted then the rest should fall into place with some sleight of hand and some tricky conditional formatting. I have also posted this current post in my previous posting (Excel 2007 - Time difference calculations & automatic insertion of text in blank column) as it is essentially the same spreadsheet problem however I could not attach the new spreadsheet. (My apologies if this should have been kept within the previous thread only).

Here goes...

Algorithm #1
=TEXT(S5,"00\:00")+(TEXT(Q5,"00\:00")<TEXT(S5,"00\:00"))-TEXT(Q5,"00\:00")

The above algorithm works out the time differences over midnight between the scheduled arrival time and the actual arrival time however, to return the correct value; the scheduled arrival time must be later than the actual arrival time. This means the train arrived earlier than scheduled. If the train arrives later than scheduled the algorithm will return a value between 1 and 1439 depending on the earlier time. 1440 appears to be spot on time (ie zero (0) minutes).


Algorithm #2
=TEXT(S5,"00\:00")+(TEXT(Q5,"00\:00")>TEXT(S5,"00\:00"))-TEXT(Q5,"00\:00")

The above algorithm works out the time differences over midnight between the scheduled arrival time and the actual arrival time however, to return the correct value; the scheduled arrival time must be earlier than the actual arrival time. This means the train arrived later than scheduled.

If the train arrives dead on time both formulas return a zero (0) which is correct as the entered time (train) was neither early nor late.

What I need is the above two formulas worked into one encompassing formula so that the time difference is returned regardless of the order of actual arrival time be it early, on time or late. I need excel to perform something similar to what follows as my logic is the train is either early or late (as spot on time using either version of the algorithm returns zero (0).

My current formula (algorithm #4 below) however is giving me grief. I am currently stuck at the following where a dead on time returns FALSE instead of zero (0). Not really too much of an issue because by playing with the </> making them <= or >= returns a numerical value. However, to really bake the noodle, the second part of the logic test does not seem to kick in

Algorithm #3
=IF(TEXT(S5,"00\:00")+(TEXT(R5,"00\:00")<TEXT(S5,"00\:00"))-TEXT(R5,"00\:00"),TEXT(S5,"00\:00")+(TEXT(R5,"00\:00")>TEXT(S5,"00\:00"))-TEXT(R5,"00\:00"))

As I said, adding an '=' so the </> changes to < or = to/> or = to returns a value of 1440 and not FALSE.

Now the algorithm looks like this:

Algorithm #4
=IF(TEXT(S5,"00\:00")+(TEXT(R5,"00\:00")<=TEXT(S5,"00\:00"))-TEXT(R5,"00\:00"),TEXT(S5,"00\:00")+(TEXT(R5,"00\:00")>=TEXT(S5,"00\:00"))-TEXT(R5,"00\:00"))

This returns the correct value for trains running late both prior to midnight and going past midnight, however the second part of the logic test does not seem to kick in for trains running early. Obviously there is something wrong with the algorithm here (even though Excel is accepting it) because if the time in column S is earlier than the comparison time in column R the first part of the logic test is obviously false, therefore should be skipped for the second part which is true.

I know I am missing something really obvious here, but I just cannot see it. Any suggestions, tweaks or modifications to the formula would be greatly appreciated.

Attached is the spreadsheet that I am banging away at with my workings on it.

Thank you in advance.

Delta

Hi

Attached is an easy solution if you can include date in your actual column.

If not, then you'll need to find a way to tell if your F16:J16 is 4hr early or 20hr late!

Regards
AC

Hi Alan,

I have rejigged the data sheet so that dates and times are now included as you suggested. Your solution works like a charm except that the on time running for trains is + or - 15 minutes either side of the scheduled departure.

How would you work that into your formula?

Hi

Let me know if Sheet2 works for you.

Regards
AC

Hi Alan,

Works like a charm. I have taken the liberty of modifying your formula to suit my entire spreadsheet - Attached.

I would love to know how the +/- formula works or how you got 0.010416667 as a 15 minute decamal vairiance.

One last question.

To make the sheet work in a copy and paste scenario raw numbers from one spreadsheet will be dumped into the "Data Dump" sheet in the attached spreadsheet.

This means that if it is a slack day and there are only a few trains running, the spreadsheet is still calculating the blanks. As all trains have a Service Number, this means that the spreadsheet does not need to calculate any row where cells in column A are blank. This however is not a fixed number and trains services vary on a day to day basis.

I can deal with this by using a conditional format like row 23, but I will have to do this for every row in the sheet for each appropriate column.

I am stuck now on wrapping some form of logic test around your =MAX(D24,E24)-MIN(D24,E24) formula to tell it not to calculate if column A is blank. The error I keep getting indicates that the logic is incomplete.

Thank you for your assistance so far. It is awesome. You and the team never cease to amaze me.

Kind regards

Delta

Sorry, forgot to attach curent working spread sheet.

Regards