Trouble with various formulas for order tracking

Hello, I'm relatively new to excel and having trouble getting a few formulas to work for me. Here is an example of our spreadsheet:

Excel.Capture.PNG

I'm having a few different issues with this.

1) How do I show the actual duration it took to receive our order? And if it was late, automatically change the font to red?

2) How do I set a column to auto generate a flag if an order is past due?

3) How do I calculate the percentage of orders that are past due?

4) How do I calculate the percentage of orders that are same day orders (ordered on the same day that they are due)?


I know it's a lot but I'm quite out of practice. Any help or guidance would be greatly appreciated!

Thank you!

-K

Hi K and welcome.

In answer to your questions:
1) Received Date - Order Date. To change the colour you need to use 'Conditional Formatting'
2) You can do this with conditional formatting too using 'Icon Sets'
3) Number past due / Total number of orders.
4) Number of orders that where due date matches order date / Total number of orders.

If you need more help than that, attach a sample workbook rather than a pic of one and we can go from there.

BSB

Pretty easy to do, but we need some more inputs. An example file with anonymous data would also help.

1) Actual duration in days is simply

Please Login or Register  to view this content.

, assuming that the columns in your picture start at A
1) Red font - you would use conditional formatting, but to help we would need to know which cells (column letters would do) should have red font
2) This is also solved by a different type of conditional formatting - icon sets
3) This is the (total number of orders where the due date = order date) / (the total number of orders) so attack it in two pieces. For simplicity, I would add another column (column K, based on your picture) to calculate the first part. In the second row of that column use the formula

Please Login or Register  to view this content.

and copy that down for as many rows as you have data. Then I am going to assume the cell where you want the answer to be is the one with the 25% in it right now. The formula would be:

Please Login or Register  to view this content.

4) This is the (total number of orders where the due date < current date) / (the total number of orders) so attack it in two pieces. For simplicity, I would add another column (column L, based on your picture) to calculate the first part. In the second row of that column use the formula

Please Login or Register  to view this content.

and copy that down for as many rows as you have data. Then I am going to assume the cell where you want the answer to be is the one with the 25% in it right now. The formula would be:

Please Login or Register  to view this content.

Hope that helps as a starter

I'm attaching a sample workbook. Hopefully this is helpful. Thank you for the quick response!!

-K

I'm confused.... Solutions to questions 1 & 2 are already in place in the attachment.

This will answer #3:

Formula:

Please Login or Register  to view this content.

This will answer #4:

Formula:

Please Login or Register  to view this content.

BSB

BSB,

Sorry for the confusion! The ones I have in place for #1 & #2 don't work in all situations. Perhaps it's just broken somewhere? I'm not sure. However, I've noticed that if the estimated duration was 0 days then no matter how many days the actual duration is, excel will not mark it as a late order and will not change the coloring of the number. So I just assumed I wasn't using an accurate formula.


Thank you so much for all the help!!

-K

Also, another problem that I've found with my current formulas is that if the order actually takes a shorter amount of time to be received than predicted, the column still marks it in red. Is there a way to fix that?

Thank you!!

-K

Try this formula in the conditional formatting:

Formula:

Please Login or Register  to view this content.

BSB

For the flagging issue, put the below formula in I5 and copy down.

Formula:

Please Login or Register  to view this content.

You can leave the conditional formatting for that column as it is.

BSB

Thank you so much!! This works perfectly!!

No problem. Happy to help

BSB