Hi , I work with an export house in India, I want to make a conditional formatting time and action table, where if I miss my sample submission date, production start date and shipment it should be highlighted. If over 3 days due , color 1 , if over 7 days due color 2. Pls.can you help me?
Absolutely, you can create a conditional formatting time and action table in Google Sheets to highlight missed deadlines. Here's how: 1. Set up your data: Create columns for: Order ID (or any unique identifier) Sample Submission Date Production Start Date Shipment Date "Days Overdue" (formula-generated) 2. Calculate "Days Overdue" for each deadline: In the "Days Overdue" column (let's say column E), enter the following formula for the first row (E2): =IF(TODAY()>C2, TODAY()-C2, 0) Replace C2 with the cell reference containing the Sample Submission Date for that order. Copy the formula down to all other rows in the "Days Overdue" column. This formula checks if the current date (TODAY()) is past the Sample Submission Date (C2). If so, it calculates the difference in days. Otherwise, it shows 0. 3. Apply conditional formatting: Select the entire "Days Overdue" column (column E). Go to Format > Conditional formatting. Under "Format cells if...", choose Custom formula is. In the formula field, enter the following formula: =$E2>3 Click Format and choose the desired color for highlighting overdue items (color 1). Click Done. Repeat steps 4-6 but with a different formula and color: Formula: =$E2>7 Color: Color 2 (for items overdue by more than 7 days) Now, your table will automatically highlight cells in the "Days Overdue" column based on the number of days past the deadlines.
Actually those tips are not new ones, there are many other videos showing them. But the examples are good and easy to understand for those who haven't noticed these hints yet.
As always ... Some really good tips Scott. I've started using Google sheets more to get svle to share easily with my team. These will really come in handy!
![[UNCUT] “บอสณวัฒน์” ลากไส้!! ใครหนุนหลัง "แม่ตั๊ก ป๋าเบียร์" I คนดังนั่งเคลียร์ I 30 ก.ย. 67](http://i.ytimg.com/vi/QlD7mU2KKRk/mqdefault.jpg)
I am adding the calendar trick right now!!!!
Awesome, Sam. Thanks for watching and sharing!
Love the dropdown menu bit. God bless u!
Glad you like it!
Thankx. Have you Any video of how to’ copy Gmails ( from one receiver ) to Google sheets automatically ? Thank you
Exchange function I like
Yes, that's a great one!
I feel QUERY and FILTER functions are absolutely great to get more done with the data
Hi , I work with an export house in India, I want to make a conditional formatting time and action table, where if I miss my sample submission date, production start date and shipment it should be highlighted. If over 3 days due , color 1 , if over 7 days due color 2. Pls.can you help me?
Absolutely, you can create a conditional formatting time and action table in Google Sheets to highlight missed deadlines. Here's how:
1. Set up your data:
Create columns for:
Order ID (or any unique identifier)
Sample Submission Date
Production Start Date
Shipment Date
"Days Overdue" (formula-generated)
2. Calculate "Days Overdue" for each deadline:
In the "Days Overdue" column (let's say column E), enter the following formula for the first row (E2):
=IF(TODAY()>C2, TODAY()-C2, 0)
Replace C2 with the cell reference containing the Sample Submission Date for that order.
Copy the formula down to all other rows in the "Days Overdue" column.
This formula checks if the current date (TODAY()) is past the Sample Submission Date (C2). If so, it calculates the difference in days. Otherwise, it shows 0.
3. Apply conditional formatting:
Select the entire "Days Overdue" column (column E).
Go to Format > Conditional formatting.
Under "Format cells if...", choose Custom formula is.
In the formula field, enter the following formula:
=$E2>3
Click Format and choose the desired color for highlighting overdue items (color 1).
Click Done.
Repeat steps 4-6 but with a different formula and color:
Formula: =$E2>7
Color: Color 2 (for items overdue by more than 7 days)
Now, your table will automatically highlight cells in the "Days Overdue" column based on the number of days past the deadlines.
Really great video, a 100% recommended
Thank you. Good tips, and useful. There was one I didn't know about and will start using.
Thanks for watching, Robert.
Thanks for your information.its really helpful
Glad it was helpful! What was your favorite tip?
Nice 🙂
I like the tip about summing a growing column 👍
So glad you enjoyed that one!
Wow, I learned a lot of useful tips. Thank you for that video.
You are very welcome, Biff.
Thank you for sharing. Good ideas !
Thanks for watching, Lina!
Outstanding, very helpful.. thank you
You're very welcome, Frank!
Wow! Those are some great tips! Thank you for putting this together!
You are most welcome, James.
Actually those tips are not new ones, there are many other videos showing them. But the examples are good and easy to understand for those who haven't noticed these hints yet.
As always ... Some really good tips Scott. I've started using Google sheets more to get svle to share easily with my team. These will really come in handy!
Love your tips and tricks, keep them coming 😉
Love your comment!
GENIUS.
Thanks!
Thank you 💛🥳
You’re welcome 😊
Google sheet full tutorial