Using IF to determine turn around times met/not met

**jozoo** · 03-20-2011, 06:06 PM

Hi everyone. I'm new here and I'll start by saying I'm not the sharpest tool in the shed when it comes to using Excel, so I would really appreciate some advice!

I have created a spreadsheet for work to work out which courier (out of 2) has the best strike rate for our company. I'm sorry but I don't know how to copy and paste it but I will try my best to describe it:

A1 B1 C1 D1 E1 F1 G1
Courier Date of pickup Time of Pickup Date arrived Time arrived Total hours Yes/No

So basically I've used a formula to calculate the total hours (TAT or turn around time) in F1 and in G1 I would like to know how to use an IF to state whether it is within 24hrs or not. Based on that, I would then also like to calculate the strike rate for each company (A1) so that each time I add another pickup, the result will go into that calculation (by chart or even just percentage rate).

If anyone can help me with this it would be great!

**JBeaucaire** · 03-20-2011, 10:51 PM

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Let's look at it together.

**jozoo** · 03-21-2011, 01:47 AM

Thanks JBeaucaire. I've attached it now.

**JBeaucaire** · 03-21-2011, 03:10 AM

In F2, then copied down:

=F2<=1

**jozoo** · 03-21-2011, 03:43 AM

Thank you so much! How did you do that? It seems so simple!

Do you know if there's any way to keep track of how each courier is going?

**JBeaucaire** · 03-21-2011, 09:11 AM

Originally Posted by jozoo

Thank you so much! How did you do that? It seems so simple!

Excel times are actually "fractions of 1" that are then formatted as time display. 12noon is .5, 6pm is .75, etc. 24 hours is thus = 1. I just needed to see your layout to confirm you were using good math in column F, which you were.

Do you know if there's any way to keep track of how each courier is going?

You're the only one who knows what that means. Transfer that knowledge onto your sample workbook above to demonstrate what you mean and I'm sure we can help further.

**jozoo** · 03-21-2011, 05:53 PM

Originally Posted by JBeaucaire

You're the only one who knows what that means. Transfer that knowledge onto your sample workbook above to demonstrate what you mean and I'm sure we can help further.

I'd like to know how to create maybe a chart or some indicator to track how many trues and falses each company (TNT and AAE in the A column) have. I have looked at charts and if it's a charting option then the 100% stacked bar chart would be best, or if not, just a percentage rate (ie: AAE - True 60%, False 40%) or something. Just something that, at a glance, would help us to decide which has the better strike rate.

I appreciate very much your help thus far and totally understand if you say 'enough'.

**JBeaucaire** · 03-21-2011, 07:04 PM

Charts aren't my specialty, but I can think of no better way to learn than for you to keep applying various styles of charts against your data.

Meanwhile, if that takes care of this original need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**jozoo** · 03-21-2011, 07:43 PM

Thanks so much. I have been playing around with charts etc so will keep going. Everything I know has been self taught (aside from problems such as these, with people like your kind self to guide me). I will keep persisting until I have worked it out!