|re: If the delivery came earlier than the expected date it would show a status of "Good" or "Ontime" if equivalent while if exceeding the expected date it would prompt "Bad" or "Delayed".|
That is exactly what both of my formulas do.
Where A1 is a cell where the user input the requested date of delivery
Please put January 1, 2015 in A1
The you said:
while b1 a cell is where the user input the date when the item was delivered
Please put January 5, 2015 in B1
In that situation, A1 is before (less than) B1, correct? Therefore the item is delayed, correct?
Let's look at my first suggestion:
January 1 (the expected date in A1 ) is less than January 5 (the delivery date in B1) therefore the formula will return "Delayed"
Now let's look at my second suggestion:
Using those same dates, this formula will also return "Delayed" because A1 (the expected date) is not greater than or equal to B1 (the delivered date).
Now, if you swap those dates (A1=January 5, 2015 B1=January 1, 2015) both of my formulas will return "Ontime" since the item was delivered before the expected date.
Please explain to me, using examples like I did, why you think my formulas will not work.
You also said: it didn't work due to that the date canot be converted to a number
Why do you say that? All Dates (and Times) are converted to numbers internally by Excel. Because the Dates are stored as numbers deep within Excel, an IF statement can compare 2 Dates directly. Please read the information found here:
Based on what you have requested, there is no need to use the MEDIAN function. I am not saying that your formula doesn't work, I am merely saying that the MEDIAN function is totally unnecessary since a simple IF function will work. You are taking a very simple comparison of 2 values (e.g. Dates) and complicating it.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.