Scheduling time problem

QuestionEdit

My question is a formula based question using2003. I work in logistics and am trying to figure out a formula so i can make a comparison between our shipping time(time goods are completely loaded on truck) with our schedule time(time the truck is scheduled to leave). The result should be a HIT or a MISS depending on whether the goods were shipped before or after the scheduled time.

Example: Scheduled Time Shipping Time 03:00 23:00(the day before) This example should result in a hit because we managed to Ship our product 4 hours before the scheduled time. However it is resulting in a MISS because of the formula im using. Current Formula is basically: IF(Shipping time <=Scheduled Time,HIT) or =IF(J13<=R13,"HIT","MISS") This formula works fine in theory but the 24 hour clock is causing me issues. The formula cant figure out if it is the day before or current day so even though 23:00 is before the scheduled time it is greater than the shipping time numerically and therefore not giving me the HIT i want.

I hope i clarified the problem and i hope you can help.

Gavin

AnswerEdit

Hi Gavin

In order for your formula - IF(Shipping time <=Scheduled Time,HIT - to work, it needs to detect the date of the shipment.

In your example, there are no date.

Scheduled Time Shipping Time 03:00 23:00(the day before)

If you use date in the schedule and shipping time, the formula will work as expected - it shows HIT.

Schedule Time Shipping Time 28/2/2010 3:00:00 AM 27/2/2010 11:00:00 PM