Datasheet form, change text to time and a more questions

[fat controller]

I am needing to design a form that allows a user to add or overwrite a number of records by copying and pasting the information from an Excel Spreadsheet (unless someone can put me right on a better way of doing this, I am open to suggestions/advice), however one of the problems is that the information being sent has times stored as text - so, for 04:45, it is simply stored as 0445 on the spreadsheet.

Is there a way to have these autocorrect once pasted into the datasheet?

Also, is there a way to make a datasheet form that copies a number of records into itself depending on a user selection?

The basics of what I am trying to achieve is:

I have a number of services that operate on a number of services (public transport), each vehicle has a unique identifier (Bonnet), and each place in the schedule has a unique identifier (RunNo); there are different schedules for different day types (Saturday, Sunday, Monday, Tuesday to Thursday, Friday and so on), and the times that each RunNo goes out and comes in differs depending on the day type.

At the moment, I have a table which has the following fields:

ID (Autonumber, PK)
DayType
Route
RunNumber
TimeOut
TimeIn

My idea/hope is that a user will be able to open a vehicle allocation datasheet for the day, select the day type (lets say, Saturday) which will then populate the relevant number of rows with the schedule information (looking at a Saturday, that would be 128 rows), and then a blank column to add to the records a vehicle ID that is being used for each RunNo for the day.

Now, on top of all this, I need to also cater for vehicles being substituted during the course of the day - - - if one breaks down, it will have to be de-allocated from the list (so presumably by changing the TimeIn to the current/actual time rather than the scheduled time) and the user will need to be allowed to add a further row to show which vehicle was used next against that RunNo.

Just to further complicate things, there is quite a number of vehicles that will have a time in which is in the following day (ie after midnight) - whilst this isn't a problem in storing the times, I also want this table to be looked up to determine whether a vehicle is currently allocated or not.

Am I pushing my luck?

EDIT - the fields for the AllocatedVehicles table are:
ID (Autonumber, PK)
RunNumber
Bonnet Number
DateOfService
TimeOut
TimeIn

It is hope that all of these with the exception of Bonnet Number can be populated by selecting the day type?

 

[fat controller]

Please ignore this - I have gone back to the drawing board, as the more I think about it, the more it appears to be overly complicated for what is needed.