# Product Release Time

We have everyday a product to be delivered one hour before mid-night, 23:00 hrs. The product is either released before scheduled time or after, sometimes past mid-night but before 06:00 hrs. The delivery team enters only time when it is delivered. I want to calculate how much earlier or later it was delivered.

Hello,

if there is no date information, you could only create a formula with making assumptions. The assumptions for the following formula are:

• Everything after `06:00` is not too late but much to early
• Scheduled time (`23:15`) is in cell `B1`
• Delivered time is in cell `A3`

Formula: `=IF((B\$1-A4)>=((6/24)-B\$1);IF(A4<=(6/24);A4-B\$1+1;A4-B\$1);B\$1-A4+1)`

See the following sample file: DeliveryTime.ods

[Update] Changed formula and file above

Hope that helps.

… and possibly better just start recording release times as full date+time like `2020-05-27 14:42:25`, with scheduled time like `2020-05-27 13:00:00` - and then just do simple math like `=A2-A1`, formatting like `[HH]:MM:SS`.

… full ack.

Hi Thanks for quicker response. I afraid that is not working for me. However I will try to work forward. In the mean time appending below the actual requirement.

Scheduled Time 23:15
Product No Delivery Time Delay Status

1 23:55 0:40 Delay

2 00:15 1:00 Delay

4 23:38 0:23 Delay

That’s how my requirement goes.

Hi Mike, Sorry to say this, the data entry team has difficulties to enter full date+time for delivery.

Ok - I’ll fix that. Please see my update …

If capturing the date of the specific scheduled delivery and of the actual delivery as well is not possible, you need to give a clear rule by what being late and being early can be distinguished. No Calc formula can change the fact that TimeOfDay is cyclic, and that therefore it cannot be concluded from two values of TOD alone which one belongs to the early point of time in the real world going on over many days.
A good way to give the rule should be to assure a maximum possible delay and a maximum amount of being early which rwo values must add up to less than 24 hours. If this cannot be done, capturing the calendaric date is indispensable.

Isn’t that said by “if there is no date information, you could only create a formula with making assumptions” ?

I didn’t want to criticize your explanations. Just took the freedom to express my thoughts with my own words - and to add a suggestion in what way the conflict might be solved reliably.