Time & Date problem

[NIMBUS]

Hi All

I am looking for a formula that will calculate the Actual Arrival Date in column in D of the examples below. I have been searching this forum for quite a while now and have tried many different if statements of my own without success. Columns A – C are imported in from an Access table over which I have no control. Row 1 is relatively simple but the actual arrival date for row 2 would be one day less than the booked arrival date whilst the actual arrival date for row 3 would be one day greater than the booked arrival date and I am struggling to get these to work.
Any help would be much appreciated.

A B C D
Booked Arrival Date Booked Arrival Time Actual Arrival Time Actual Arrival Date
01/06/11 21:00 23:00
02/06/11 00:40 23:30
01/06/11 22:20 00:50

 

[Brianwarnock]

You say that the second row will have a date 1 day before the booked, I presume that is because you believe that the arrival cannot be 20+hours late, ditto your comment for row 3, all this shows is that the data is badly designed.All you can do is select a cut off point for late/early arrival and work with that to determine whether the date needs to be modified.

Brian

 

[NIMBUS]

Thank you Brian for your quick reply. I suspected this may not be possible but as I said I do not have control of how the table I import from is laid out. As an alternative I have tried to calculate just the time difference between the booked arrival time and the actual arrival time but still struggle when the times cross over the midnight line so I would be gratefull for any help anyone can give with this

 

[thingssocomplex]

Can you post a mock sheet with some sample data please?

 

[Brianwarnock]

You say that you want to calculate the arrival date, but without a max deviation from booked time no calculation can be done the arrival could be from on time to days early or late.

Brian

 

[NIMBUS]

Sorry for the delay in replying I have been away for a few days without internet access.
So to try and explain what I am looking for a bit better.
We have units arriving everyday. In an Access Database table, each unit has a booked arrival date and booked arrival time. The actual arrival time is recorded as each unit arrives. I then import this data from the database into an excel spreadsheet (see attached example) via a database query in excel. There is lots more data in t he table which is not relevant to this issue.
The units can arrive up to 5hrs before or after the booked arrival time. If outside of this 5 hrs they are cancelled.
Originally I was looking for something to work out the actual arrival date of each unit which is simple when they arrive on the booked date but often they can be booked to arrive before midnight but actually arrive after midnight and vice-versa so I guess I was looking for something like ‘If the booked arrival time = before midnight and if the actual arrival time = after midnight then the actual arrival date = the booked arrival date +1 ‘ if that makes sense.
Having being advised by Brian Warnock that this may not be possible or be difficult to achieve I am now looking at a different way of getting the info I require. This would involve working out the time difference between the booked and actual arrival times in hrs:mm Again this is quite simple when the units arrive on the booked arrival date but, as in row 3 of the attached example, when a unit is booked to arrive after midnight but actually arrives before midnight I get a line of #hash symbols as the result is a negative time. On the reverse when a unit is booked to arrive before midnight but actually arrives after midnight, as in row 4 of the attached example, the difference does not show correctly.
Thank you for any help on this.

 

[Brianwarnock]

Now that you have given the window I asked for it is simple as if arrival time +5 hours is less than booked time you have moved to the next day.

You cannot enter times in the formula so you need a working column, which if you still want to calculate the time difference will also contain 23:00 and 1:00 as you cannot enter 24:00 it gets converted to 00:00.

I will look at your spreadsheet later.

Brian

 

[Brianwarnock]

Here it is, hope that you can understand it. It also allows for deliveries a day earlier as per your second row.

Brian

 

[NIMBUS]

Brian that does the job very nicely. Thank you very much for your assistance.Much appreciated.