Excel 2007 :: Time Difference Calculations And Automatic Insertion Of Text In Blank Column

Please find attached a daily copy of a spreadsheet that is used to monitor train running times.

The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.

The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.

Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.

For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)

What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.

To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.

My problem is two fold.

Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.

Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.

DAILY TRAIN RUNNING.xlsx‎