if formula question

[blakeboyuk]

Good morning,

Unfortunately the wind whistles through my ears at times and I struggle to comprehend even basic excel formula logic.

One such example is that I have a number of scenarios below which determine whether an order is defined as a HIT or a MISS.

The main question is was the part ordered and picked before the cutoff meaning its a HIT. This gets a little more complicated and I need to find a formula to account for orders placed after cutoff and picked the following day which are also HITS. When I've been attempting this thus far I get "too many arguments for this function" error message.

Can any one help offer some advice on how to do this?

Thanks so much

David

Order Number	Order Placed?	Pick Date	Pick Time	Status
110354220	Ordered after cutoff	Picked the following day	Picked before cutoff	HIT
110250140	Ordered before cutoff	Picked on the same day	Picked before cutoff	HIT
110307676	Ordered before cutoff	Picked on the same day	Picked after cutoff	Miss
110366617	Ordered after cutoff	Picked the following day	Picked after cutoff	Miss

 

[etaf]

can you show the formula you tried
I need a lot more information to assist

I assume the cells have dates in and not those words

What are the cutoffs , how does excel know the times to use
Ordered on 5th Aug 2020 at 11:15 ,
whats the cutoff , is it 24:00 hour operation ?
So if ordered at 23:00 on one day and picked at 01:00 on next day - 2 hour elapsed time - is that picked before cutoff ?

Order placed , is that a date and time in the cell
Pick Date , is just the Date
Pict time , is just the time so these are split into two columns

each order number is only 1 row , you don't have multiple rows for the same order

 

[sykes]

Hi, and a belated "Welcome to Mr. Excel" !!

Assuming your order number starts in column A, and your actual data starts in row 2...

Use this formula in E2, then fill down (with the fill handle):

=IF(OR(AND(B2="Ordered before cutoff",C2<>"Picked on the same day"),AND(B2="Ordered before cutoff",C2="Picked on the same day",D2="Picked after cutoff"),AND(B2="Ordered after cutoff",C2<>"Picked on the same day",D2="Picked after cutoff")),"Miss","HIT")

 

[blakeboyuk]

Thanks very much Sykes & Etaf for your responses.

Skyes, yes that has worked. fantastic! So an IF/OR/AND formula combined was the answer.That's something I'm not familiar with so I'm going to try to break down what you have done so I will know for next time.

Many thanks once again, David.

 

[sykes]

David
I would point out that my solution wasn't so much THE answer, as just ONE solution. There are usually many ways to achieve the same goal with Excel, and very often I find that others come up with solutions which are both shorter AND slicker than mine, so you may find that other options become available...

For info, the 3 AND functions evaluate the conditions within their brackets (I believe you can test up to 255 conditions at a time!). For an AND to return TRUE, ALL conditions within its brackets need to return TRUE.
The OR function will return TRUE if ANY of the conditions within it return TRUE, so if ANY one or more of the 3 AND conditions returns TRUE, the OR returns TRUE, and consequently the IF returns TRUE, and we end up with a "Miss" in Column E.

Looking at your requirements, I think there are only 3 conditions where you fail to achieve your dispatch targets:
1. The buyer orders in time, and you don't pick the item that same day.
2. The buyer orders in time, and you pick the item the same day - but not before the day's deadline.
3. The buyer orders too late (so you have until the following day to pick) but you miss the following day's deadline.

These are the 3 AND conditions which are tested for.
If ANY return TRUE, the OR tells the IF to display "Miss" otherwise it displays "HIT."

HTH

Pleasure to help, and thanks for the feedback.