Formatting ordering system in Excel

Question

Hey, would appreciate any help.

I'm trying to set up a colour coordinated re-ordering system in excel, based off last purchase date, re-order time and lead time.

Basically I need a coloured cell (degree of urgency - red, yellow, green), which triggers as a response from a pre-set number of days ('re-order frequency' minus 'lead time'), taking into account the last date the order was placed, and the current date (posted 10/02/23).

Example:

Product: Last Order Date: Re-order Frequency (days): Lead Time (days): *Colour:

#1 01/02/23 14 1 green

#2 01/02/23 16 6 yellow

#3 01/02/23 20 13 red

*Colour: - Green would be anything 2+ days before the product needs reordering

          - *Yellow* anything within 2 days +- the pre-set re-order time

          - *Red* anything 2+ days late

I imagine that every row will needs its own formula due to the differing re-order/lead time, but if it makes it easier to understand:

Product #1: 'Colour' = 'LastOrderDate' + 'ReorderFrequency' - 'LeadTime' => green (because it still has 3 days before needing to be reordered)

Product #2: 'Colour' = 'LastOrderDate' + 'ReorderFrequency' - 'LeadTime' => yellow (because it only has 1 day before needing to be reordered)

Product #3: 'Colour' = 'LastOrderDate' + 'ReorderFrequency' - 'LeadTime' => red (because it is 2 days late in being reordered)

Hope this makes sense.

Answer 1

Hello Lachie88

Welcome to Microsoft Community.

After observing your description, I made a corresponding table in Excel for this, as follows:

Please note that I have changed the Las Order Date, this is to better match the date of the day and to highlight the different colours, e.g. today is 2/10/2023. If your condition is 01/02/2023, the last colour will only be in red.

To accomplish this, you can create a formula in a separate column (let's say Column E) to calculate the reorder date for each product based on the "Last Order Date," "Re-order Frequency (days)," and "Lead Time(days)". The formula for each row would be as follows:<br>For Product #1 in Row 2: =B2+C2-D2For Product #2 in Row 3: =B3+C3-D3For Product #3 in Row 4: =B4+C4-D4

Next, create a formula in a separate column (let's say Column F) to get the yellow, then type the following formula:<br><br><br><br>=IF(TODAY()-E2<-2, "Red", IF(TODAY()-E2<=1, "Yellow", "Green"))<br><br><br><br>Place the cursor in the bottom right corner of cell F2 -> drop down the batch application -> this will generate the corresponding colour for the different days gap <br><br> <br><ul><li><p data-prewrap="true">For Red, where E2 shows 2/20/2023, this is clearly beyond the 2/10/2023 period, which equates to 2 days late in being reordered, to avoid overflowing cells I have changed the condition to show red for all days greater than 2 </p><p data-prewrap="true"></p></li><li><p data-prewrap="true">For Yellow, where E3 shows 2/11/2023, which is obviously one day before 2/10/2023, which is the same as 1 day before needing to be reordered </p><p data-prewrap="true"></p></li><li><p data-prewrap="true">For Green, where E4 shows 2/1/2023, which is obviously nine days away from today 2/10/23, the same principle as before.</p></li></ul>

Select the "Colour" cells (F2:F3).<br><br>Go to Home > Conditional Formatting > New Rule > Select Format only cells that contain -> Specific Text ->containing -> Red. The same steps for Yellow and Green -> As shown below:<br><br>

I hope this helps, I have sent the sample file in your private message, please view it here:

Best Regards,

Chandy |Microsoft Community Support Specialist

Answer 2

Hello Chandy,

That's perfect, exactly what I'm after, thanks a lot.

Cheers,

Lachie