Using conditional formatting to show status of shipments.

Good morning Excel Forum,

I need help with conditional formatting, maybe even using offset. Here's my dilemma;
- Trying to figure a way to show a status of over due in column K, using the scheduled date in column G against todays date. If possible having a transitioning color (green to red) would be better great. I am required to ensure our shipments are on time and the previous manager had no way to track these shipments. Also is there a way to number the row and skip the blank rows? There are several more rows in the original file. Thank you in advance for your help!

Rick

try the attached see the conditional formatting - viewing it is easier than explaining it - uses simple formula in cell that gives days between Today() and date uses this figure to establish a max/min for the colour gradient.
simple row formula in column 'A' - the format is a 'structured table' so all formatting/formulas etc., dynamically expand when you fill the row below the table - type in column 'B' first table then expands - column 'A' will look after itself.

I appreciate the help. I think everything is great. My only other question would be, Is there an easy way to differentiate and calculate the different types? Not every shipment will have a sub or just two. Also I would need all negative days to show as red.

Using the original file try applying the following to K12:K40 =AND(B12<>"",K12<TODAY())
Let us know if you have any questions.

I feel that if we had more information about "I don't think that will work" we might be able to come up with other options.
I suggest uploading another file that shows what your data looks like at the starting point and as I assume Sheet1 is the desired end point.
Also, there is no explanation of what constitutes "could be late" in the original file, so we don't know when yellow fill should be applied.
Let us know if you have any questions.

See if the following fulfills the request:
1. Modify conditional formatting rule to read: cell value < 0
2. For Total # of Products: =COUNTIFS(Table2[Manager],"<>")
3. For Total # of Subproducts: =COUNTA(Table2[Product])-K2
4. For On Track: =COUNTIFS(Table2[Status],">=0")
5. For Overdue: =COUNTIFS(Table2[Status],"<0")
6. For cells K9:K18 =IF(LEFT(J9,5)="total",SUMPRODUCT(--(Table2[Group]=TRIM(RIGHT(J9,LEN(J9)-SEARCH(" ",J9))))),K8-1)
Note that a new column H ([Group]), which could be hidden for aesthetic purposes, has been added to the table.
Let us know if you have any questions.