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.
Someone please help me with formula.
Hello,
if there is no date information, you could only create a formula with making assumptions. The assumptions for the following formula are:
06:00 is not too late but much to early
23:15) is in cell B1
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.
If the answer helped to solve your problem, please click the check mark (
) next to the answer.
… 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
3 22:50 -0:25 Advance
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.