Determining Late collections over midnight....

Hi All,

I have reviewed multiple threads on this subjection but cannot find one that answers my question.

I have a set collection time and need to calculate if it was collected on time, early or late problem exists wehn tring to calculate this over midnight. There are no dates in the times that are recorded.

Criteria as follows -

Planned collection time in F6, actual collection time in G6, any collection made 45 minutes after the planned time is late, anything between planned time +45 mins is on time anything before planned collection time is early.

=IF(ISBLANK(G6),"",IF(G6>(F6+TIME(0,45,0)),"Late",IF(G6<F6,"Early","On Time")))

When i have a collection time planned in the early hours of the morning i.e. 01:00hrs and my actual collection time is 22:30hrs it flags as late but is actually early.

Can anyone solve ? Appreciate your help....

Understand what you are saying but adding dates into the planned collection and actual collection time is not possible.

If a collection was to be 23:30hrs late it would be removed as this is a daily report.