Conditional Formatting comparing two dates
I'd like to create a conditioning formatting to highlight a cell based on the difference between two dates. Column A is the expected date of delivery. Let's say 1/1/23. Column B is the actual date I received it, say 1/5/23. I'd like it to follow this rule:
Highlight Column A cell when:
Received date is more than 5 days before expected date = green highlight
Received date between 5 days before and 5 days after expected date = yellow highlight
Received date is more than 5 days after expected date = red highlight
This was I can track if materials are being delivered in a timely manner.
Best Answers
-
@Ksephora You would need to create a helper column to first calculate the days before/after received date. You can then use this column as the condition for the formatting.
-
Hi @Ksephora,
First, create a helper column to get the amount of days in-between the dates. Use this formula (make it a column formula).
=[Received Date]@row - [Expected Date]@row
Next, setup 3 conditional formats.
Here is what it looks like - make sure to set the formatting on the correct column for your needs.
Hope this helps,
Dave
Answers
-
@Ksephora You would need to create a helper column to first calculate the days before/after received date. You can then use this column as the condition for the formatting.
-
Hi @Ksephora,
First, create a helper column to get the amount of days in-between the dates. Use this formula (make it a column formula).
=[Received Date]@row - [Expected Date]@row
Next, setup 3 conditional formats.
Here is what it looks like - make sure to set the formatting on the correct column for your needs.
Hope this helps,
Dave
-
DKazatsky2 and JamesB thanks so much! That worked perfect.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!