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
Bookmarks