Time between two times

S

sherbetdab

New member
Joined
Jan 6, 2017
Messages
18
Reaction score
0
Points
1
Location
Glasgow, Scotland, UK
I have a sheet with columns for a planned arrival time window, I have a cell with actual arrival time.

I have to work out if the actual arrival time is within the planned arrival time window.

All is going well until i have times which span 2 days.

Actual arrival is 23:00 in cell D1
Earliest arrival is 22:00 in cell B1
Latest arrival is 01:00 in cell C1

The code returns Not, No and Late but i need it to return On Time, No and No

Can anyone help me out?

Code: 
With Range("E1:E" & LastRow)
    .Formula = "=IF(AND(D1>=B1,D1<=C1),""On Time"",""Not"")"
    .Value = .Value
End With
    
With Range("F1:F" & LastRow)
    .Formula = "=IF(D1<B1,""Early"",""No"")"
    .Value = .Value
End With

With Range("G1:G" & LastRow)
    .Formula = "=IF(D1>C1,""Late"",""No"")"
    .Value = .Value
End With
 
Last edited:
I can think of at least a dozen different scenarios with this.
Attaching a sample workbook with numerous situations would be advantages.
 
Nos is right.

The safest and most robust solution is to include date and time information in all 3 cells (columns B,C and D). Your formulae will all work correctly, your window, can be as big as you like, you actual arrival times can be any time/date.

At first sight you may think, oh well, if the latest arrival time is less than the earliest arrival time then add 1 (day) to the latest arrival time. That'll work. Sometimes.
What if the Earliest arrival time is 00:10 and the Latest arrival time is 03:00, and the actual arrival time is 23:55? Without date information your formulae will say he's late by 20hrs 55mins, but was he only 15mins early?
Worse, what if there's a 23 hour window of 01:00 to 23:00 and the actual arrival time is 00:30? Is he half an hour early or 1 and a half hours late?
 
Last edited:
.
Here is one way ... but it is only good for one 24 hour period. (In other words, if the two times are separated by more than 24 hours, it won't work.)

Code: 
 =IF(A2="","",MOD((TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($B2,"pm"," pm"),"am"," am"))))-(TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,"pm"," pm"),"am"," am")))),1))



A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
O​
1​
Start​
End​
Total​
2​
9:23pm​
2:14am​
4:51​
THIS IS VALID FOR A SINGLE 24 HOUR TIME PERIOD. IF THE TIMES EXTEND BEYOND 24 HOURS FROM ONE ANOTHER
3​
7:23am3:30pm
8:07​
THIS WILL NOT WORK.
4​
11:00am1:00pm
2:00​
5​
8:00pm10:00am
14:00​
6​
7​
8​
FORMULA IN C2 AND COPIED DOWN :
9​
=IF(A2="","",MOD((TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($B2,"pm"," pm"),"am"," am"))))-(TIMEVALUE(TRIM(SUBSTITUTE(SUBSTITUTE($A2,"pm"," pm"),"am"," am")))),1))​
10​
11​
12​
* Columns A & B formatted as GENERAL. Time must be entered as shown in cell (not 24 hour time).
13​
* Column C formatted as TIME 13:30


Otherwise, I agree with the other contributors ... include a date with your time entries. Makes it so much simpler.
 
You must log in or register to reply here.
Back
Top